Excel tips and tutorials
To make it easier to people to enter data in your Excel workbook, you can create drop down lists in the cells, by using Excel data validation. These lists will also help prevent invalid entries in your worksheets.
Continue reading “Shortcuts to Create Drop Down List in Excel”
There are instructions on my Contextures website for using a combo box with data validation cells. Click on a cell that contains a data validation list, and the combo box appears.
In Monday’s blog, you saw how to make simple dependent data validation drop down lists. After creating the drop downs, you added some flexibility by using the IF function in the data validation formula. See a couple of problems that can occur when you refer to other cells in your data validation, and those cells are blank.
Continue reading “Ignore Blank Problems in Excel Data Validation”
You can use data validation to create drop down lists in Excel. With a bit of Excel magic, you can create dependent drop down lists, so the selection in one drop down controls what appears in the next drop down. You’ll see different drop down lists in the same cell!
Continue reading “Different Drop Down Lists in Same Excel Cell”
Why are invalid entries allowed in Data Validation sometimes?
Have you ever set up a data validation drop down list, so you can select valid items from a drop down list. But instead, Excel allows people to type anything they want into that cell?
See why that happens, and how you can prevent the invalid entry problem.
An Excel data validation drop down list only shows 8 items at a time, and with a long list of items, it might take you a while to scroll through that long list.
To make data entry easier, see how to shorten data validation list, by using a macro.
Continue reading “Shorten Data Validation List With Excel Filter Macro”
There’s a sample Excel workbook on my Contextures website that uses a bit of Excel VBA to automatically add new items to an Excel data validation drop down list.
For example, if the drop down list shows Apple, Banana and Peach, you can type Lemon in the data validation cell.
Then, as soon as you press the Enter key, Lemon is added to the named range that the data validation list is based on.
The source list is sorted too, so that Lemon appears between Banana and Peach.
Someone emailed me last week, and asked if I would explain how the Excel VBA code works.
It rained (and even snowed a little) on Friday, so it was a good day to stay in, and work on a new page for the website.
If you’re interested in setting up a similar file, you can wander over to my Contextures website, and read Excel Data Validation – Add New Items.
Here’s a short video that demonstrates the file, and briefly explains how the code works.
You can watch this, if it’s too early in the day to read about Excel VBA code.
If you’d rather just play with the file, and figure it our for yourself, you can download the workbook from my Contextures website.
Go to the sample workbooks page, and in the data validation section, look for DV0021 – Update Multiple Validation Lists
It’s in Excel 2003 format, and contains macros. You can enable macros if you want to test the code.
___________
Cascading lists and kangaroos? Today, Ed Ferrero shares his technique for creating dependent data validation from pivot tables. Ed’s from Australia, and it looks like we’ll learn a bit about his country too, as we go through his sample file.
We’ve created dependent data validation drop downs before, based on named ranges, or sorted lists. Ed’s technique is perfect if you have a large data source, and it isn’t sorted in the order that you need.
In this example, there’s a list of States and Cities, with the cities in alphabetical order.
Ed created two pivot tables, one with State in the row area, and one with State and City in the row area.
The State labels don’t repeat in the pivot table, so you can’t use the sorted table dependent data validation technique.
Instead, Ed created a couple of named ranges, and some dynamic ranges.
Tip: If you reduce the worksheet zoom to 39%, you can see the range names.
The first dynamic range is for the City heading in the second pivot table.
The next two dynamic ranges, StateNo and StateCityNo, use relative references to read the value of the state from the cell to the left of the active cell. For example, if the selected State is in cell A3 on Sheet1, these formulas are used:
Queensland is the selected State, so StateNo =3 and StateCityNo =5.
Then, the next State is found in the StateCity range.
The next State is South Australia, and it’s in row 9, so StateCityNext =9.
Finally, the dynamic range for the list of cities is created.
The City range is offset from the CityHeader cell, 5 rows down, 0 columns right, 4 rows high (9-5), and 1 column wide.
The final step is to create the data validation drop down lists. In cell A3, a State drop down list is created, based on the State range.
In cell B3, a dependent City drop down list is created, based on the City range.
You can download Ed’s sample file to see how it works: Dependent Data Validation From Pivot Tables. It’s a zipped file, in Excel 2003 format.
Ed maintains an Excel techniques web site at www.edferrero.com. He is based in Australia, and has been a Microsoft Excel MVP since 2006.
____________
If you’re having a party this weekend, you can plan your party seating with Excel. Get this sample Excel seating workbook, enter the guest names on the Lists sheet, then fill the tables by selecting names from data validation drop down lists. After you’ve assigned a guest to a table, that guest’s name disappears from the drop down lists, so you can’t accidentally assign a guest to two different seats.
NOTE: There is a newer seating plan here: Excel Seating Plan with Charts
You added some data validation drop down lists to your Excel worksheet, to make it easier to enter the data. Later, you opened that workbook, and the data validation arrows are missing! What happened, and how do you get those arrows back?
