|
|
By Debra Dalgleish, on April 2nd, 2013 To make data entry easier, you can create a drop down list in an Excel cell, using data validation.
This works well with a list of words, but the list doesn't show symbols if you create the list in a font such as Webdings. In the example below, I entered 5, 0, and 6 in cells B2:B4 and formatted the cells with Webdings.
The drop down list shows the number values, instead of the formatted symbols.
Use Built-In Symbols
You can’t create a drop down list of symbols based on formatted cells, but you can use some well-hidden
Continue reading Create a Drop Down List With Symbols
By Debra Dalgleish, on March 21st, 2013 Yesterday, one of my clients emailed to let me know that she was having trouble entering January dates in a file that I had created. My first guess was that there was an issue with the regional settings, because her company uses the dd/mm/yyyy format.
But when I tried entering a January date, with my mm/dd/yyyy settings, I got an “Invalid date” message too.
Limit Dates with Data Validation
The date that I had entered – 1/3/13 – was a valid date and in a valid format, so I checked the data validation settings. And that’s where I found
Continue reading Limit Date Range with Excel Data Validation
By Debra Dalgleish, on March 12th, 2013 One of my Excel sample files lets you double-click on a cell that has a data validation list, and a combo box pops up. The combo box has advantages, because you can set the font size, and the number of rows in the drop down list, and the entry autocompletes as you type.
However, one of the disadvantages is that the combo box lets you type anything, even if it’s not a valid entry from the data validation list. In the combo box shown below, I was able to type “Cold” as a month name.
And even
Continue reading Validate Entries in Excel Combo Box
By Debra Dalgleish, on February 21st, 2013 When you’re setting up data validation on a worksheet, you can include an Input Message, to help anyone who’s using the workbook.
You’ll have to get to the point quickly though – the message is limited to 255 characters.
There are other limitations too – you can’t control the size of the text box, and you can’t change its font size or fill colour, unless you change your Windows settings.
Show Input Message in a Text Box
As an alternative to the Input Message popup, you can show a message in a text box, at the top of the
Continue reading Show Data Validation Message in Text Box
By Debra Dalgleish, on November 1st, 2012
You can make data entry easier by adding a drop down list on an Excel worksheet.
In most cases, you want people to select an item from the list, to prevent typos and invalid entries. If they try to type something that's not in the list, they'll see an error message, and will have to try again.
Allow Non-List Entries
In some workbooks though, you might want to allow other entries in those cells, in addition to the items in the drop down list.
To allow other entries, you can turn off the Error Alerts in the
Continue reading Allow Other Entries With Excel Drop Down List
By Debra Dalgleish, on October 23rd, 2012 With Excel's data validation, you can restrict the dates that can be entered on a worksheet. For example, you could specify start and end dates on the worksheet, and only dates within that range can be entered.
In the screen shot below, start and end dates are entered in column E, and dates in column B must be within that date range.
Set Up the Data Validation
After entering the start and end dates on the worksheet, follow these steps to set up the data validation:
Select the cells where the data validation will be applied –
Continue reading Restrict Date Entries with Data Validation
By Debra Dalgleish, on September 25th, 2012 When you create a drop down list with data validation, you can't change the font or font size. If you have reduced the zoom setting for a worksheet, it can be difficult to read the items in the list. And even at 100%, it can tough to read the tiny print, at the end of a long workday.
Zoom In to Read the List
To make the text appear larger, you can use a bit of VBA code to increase the zoom setting when a data validation cell is selected. (Note: this can be a bit jumpy)
Continue reading Make Data Validation List Appear Larger
By Debra Dalgleish, on August 30th, 2012 To make data entry easier, you can create a drop down list of items in a worksheet cell. Then, instead of typing a product name in an order list, you can select a valid product name from the list.
However, in some worksheets, when you click the arrow to open a drop down list, the selection goes to a blank at the bottom of the list, instead of the first item in the list. That can be annoying if you have to scroll back to the top of a long list, where you have the put the most popular
Continue reading Excel Drop Down Opens At End
By Debra Dalgleish, on July 26th, 2012 If you're building a budget in Excel, you can limit the total amount that is entered, to help prevent anyone from going over budget. I shared this tip a few years ago, in this blog post: Limit the Total Amount Entered in Excel
New Features
In the new version, I've added a few more features, to help you fill in the correct amounts.
Below the Budget Limit, in cell D3, you can see the amount that hasn't been added to the budget yet. In column D, you can see the maximum amount that can be entered in each
Continue reading Control the Budget With Excel Data Validation
By Debra Dalgleish, on July 3rd, 2012 When you build an Excel tool or template, it's rare that you're ever really finished building. There's always something that would make the tool a little better, either for your own use, or for your customers.
And that's the case with the Excel worksheet data entry form, which I've just updated again. The original version was by Dave Peterson, and the form has evolved into a version in which you can add and update items in the database.
New Features
In the latest version, I fixed an issue with the navigation. Thanks to Travis, who let me know about the
Continue reading Check Excel Database Before Adding New Item
|
|
Recent Comments