The other day I was reading a post over at the Contextures blog about Dynamic Dependent Excel Drop Downs and realized that using an Excel Table would provide an alternative method that is both simple and flexible. Tables are available in Excel versions 2007, 2010, and 2011.
In this post I’ll create a Table to hold the Category’s and Items, create three defined names using dynamic formulas, then use Data Validation to create two drop-down lists, the second being dependent upon the first.
Create at Reference Table
Here’s a Table with Category names in the header row and Items in the columns. I just typed in the information then converted to a Table.
Create a Dynamic Defined Name for Category List
Create a defined name for the Table1 Header row range by using the formula =Table1[#Headers]. Please note that Table1 is the name of the Table created in the step above.
This defined name is dynamic, meaning it will expand when more columns are added and shrink if any columns are deleted. It will return the header row of the Table, which we’ll use in the next step. I used myCategory for this defined name.
To create a defined name in Windows choose Formulas > Name Manager then click New…. On a Mac choose Insert > Name > Define…. This will bring up the New Name dialog box that looks like the Edit Name screen-shot shown above.
Create a Category Drop Down List with Data Validation
Type Category in cell A1 for the column heading. Next select cell A2, then choose Data > Data Validation > Data Validation… and in the Data Validation dialog box (shown below) select List from the Allow box, then type in =MyCategory in the Source box, and click OK.
Now cell A2 has a drop down button that shows the Header row of the Table. (Don’t worry about extending this Data Validation down to more rows, we’ll take care of that later.)
Column B will hold a drop-down list for the Item, which is dependent upon the Category. This takes two defined names to work properly.
Create the First Defined Name for Items
[Update: Select cell B2 before you follow this next step.]
Create a defined name with the following formula:
that I’ll name myItemList.
This formula will return a reference to the Table column that matches the Category selection in cell A2 on Sheet1.
If I use Data Validation to create a drop-down list with the myItemList defined name I’ll get eight items returned because Table1 has eight data rows. As you can see in the picture below, the Vegetables item list has two blank lines, and the Other Stuff item list has one blank line. Not an elegant solution.
However, if you have a table that always has equal items (rows) in each column then this defined name formula will will work well for a Data Validation list.
Create a Second Defined Name for Items
We can create a second defined name that will give us a dynamic list with the exact count. The following OFFSET formula will do the trick.
This formula uses the first defined name (myItemList) but alters the height by counting the items. I named this defined name myItem, as you can see below in the Name Manager screen shot.
Create an Item Drop Down List with Data Validation
Type Item in cell B1. Select cell B2 and open the Data Validation dialog box. Choose List and enter the following formula =myItem, then click OK.
The result is a dynamic drop-down list in the Item column that’s dependent on the Category selection in column A, and returns the exact list.
Convert to a Table
Now it’s time to covert this to a Table, and by doing so the Data Validation will be preserved and automatically expand with the addition of more rows.
Select cell A2, then in Windows (Excel 2007, 2010) choose Insert > Table, verify the information in the Create Table dialog box, and click OK.
On a Mac (Excel 2011), select cell A2 then choose Tables > New > Insert Table with Headers.
At this point I normally turn off the Data Filter because it’s rather annoying.
As your Table expands, with more rows or columns, this dynamic drop-down list will work just fine. To create a new row in the Table with Data Validation press the Tab key while the active cell is the last column of the last row.
Potential Problem with OFFSET
As you might have guessed the OFFSET formula depends on having items at the top of the list. Should your data have blank rows in the middle of the column, the drop-down won’t have all the items listed yet show blank cells in the list.
In this case it’s best to use the first defined name (myItemList) in creating a dependent, drop-down list for Item. At least you’ll get all the data, even if it does have some blanks in the list.
Reference Table Location
For illustration purposes I’ve shown the reference Table on the same worksheet as the Category and Item Table. Normally I would place the reference Table on a different worksheet. In this instance none of the formulas would change.
I’ve had numerous questions about the details of this post so I’m putting a link here to download the file.