Conditional Drop Down Lists in Excel

It’s fairly easy to make one drop down list show items based on what was selected in another drop down. But what about making a third drop down list? And how can you work with spaces, or other characters that can’t be used in Excel names?

Here is a nice, simple conditional list – if you select Red Fruit in cell A2, you can see a list of red fruit in cell B2.

dependentdropdownspace01

Using Named Ranges

There is a named range on the worksheet – RedFruit – and the data validation formula uses the INDIRECT function to create a reference to that range. The formula also removes any spaces in the text, by using the SUBSTITUTE function, because the range names can’t use space characters.

=INDIRECT(SUBSTITUTE(A2," ",""))

dependentdropdownspace02

Lookup Tables for Complex Lists

It’s simple to use the SUBSTITUTE function to get rid of any spaces, but your list of items might contain other characters that can’t be used in range names, such as an ampersand (&) or asterisk (*).

Instead of trying to substitute every illegal character, you can set up a lookup table. Put the list items in the first column, and a short code for each item in the second column. The range with items and codes is named ProductLookup.

The data validation formula will look in that list, to find the code, and show the items from a list with that name. In this example, all the list names end with “List”, so that is added in the formula.

=INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"List")

dependentdropdownillegal06

Add a Third Dependent Drop Down

You could even add a third drop down list that is dependent on the selections in the first two.

dependentdropdownillegal01

Just create a lookup table for each of the secondary lists, and an item list for each of those secondary codes.

dependentdropdownillegal02

Here is the data validation formula in cell C2

=INDIRECT(VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"Lookup"),2,0)&"List")

It finds the product lookup table,

  • VLOOKUP(A2,ProductLookup,2,0)&"Lookup"

the product code in that table,

  • VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"Lookup"),2,0)

and adds “List” to create a reference to the list name.

=INDIRECT(VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"Lookup"),2,0)&"List")

Download the Sample File

You can see the details on my Contextures website, Dependent Lists page, and download the sample file.

_________