Dependent Drop Down Box
How to do one of those trickier tasks in Excel: set up a drop down box that is dependent on the result of another drop down box.
If coffee is selected from drop down A then I want a list of coffee types to appear in drop down B. If Tea is selected in drop down A then I want a list of tea types to appear in drop down B.
In the real world these lists may be product type and sub-products, departments and employees, account groups and sub accounts etc.
I’ve saved a file here for you to download and see how I’ve done it.
Note how the primary table has a column containing the names of the associated secondary tables
To do this highlight the 3 cells Tea, Coffee, Juice and go to the Name Manager Box above column A and type ddDrink and then press Enter
Click on a cell, lets say G21 and select Data > Validation > From List and type =ddDrink in the source box
Here’s the trickier part
Let’s say we want our secondary drop down box to be right next to our primary drop down.
So we click on cell H21, and now we are going to create a named range called ddSecondary
To do this, click on Formulas > Define Name and name it ddSecondary
then add this formula to the Refers to: box
=INDIRECT( INDEX( tblDrink[Secondary Table],MATCH( G21, tblDrink[Drink],0)))
That’s a pretty nasty looking formula when looked at in one go.
The INDIRECT part is the “clever bit” in that it indirectly gives you the table name to be used for the second drop down.
For example, if Tea is selected in G21 then the INDEX MATCH part looks up the word Tea in the Drinks Table and then returns the corresponding Secondary Table name of tblTea.
The INDIRECT part then uses the reference to tblTea to provide a list to be used by the Secondary Drop down box.
Not easy to understand on first pass, Give it a try and feel free to ask for help.
Click on cell H21and select Data > Validation > From List and type =ddSecondary in the source box
A very useful extra step is to add conditional formatting to flag invalid combinations such as Flat White Tea or Apple Coffee
To see how I’ve done that take a look at the demo file, essentially I use a COUNTIF formula in the conditional formatting.