Multiple layers of drop down menus for EXCEL

Question

I want cell A1 to be a drop down menu (which I know how to do), and then cell B1 to be a drop down menu based off that initial selection.

Example: If I select in A1 "Entertainment," in B1 I want a drop down menu that lets me select "Movies, Music, Sport". But if I select in A1 "Food," in B1 I want a drop down menu with "Dairy, Veggies, Fruit, Meat" etc.

I know basic coding, but I'm mostly looking to use Excel's capabilities.

Thanks! - Laura

Answer 1

You can create this with some simple formulas. Here's an example set up:

1. Create a new workbook with three worksheets "Main", "First Layer", "Second Layer".
2. In First Layer, enter in column A: Entertainment, Food.
3. In Second Layer, enter in column A: Movies, Music, Sport. Enter in column B: Dairy, Veggies, Fruit, Meat.
4. In Main, configure the cell validation of A1 to "list" with the source ='First Layer'!$1:$1.
5. In Main, configure the cell validation of A2 to "list" with the source =IF($A$1="Entertainment",'Second Layer'!$A:$A,IF($A$1="Food",'Second Layer'!$B:$B,""))

This should get you started.

To make the whole thing a bit more advanced, you might want to use a combination of the VLOOKUP and the INDIRECT functions to access the right column in the cell validation list source. Just post a comment if you would like me to extend the example in this direction.

Answer 2

The trick is using indirect function.

Here is the tutorial.

The basic steps are

1. Create a reference table in Excel
2. Name range of the 2nd layer menu items as the value of 1st layer menu item.
3. Use =indirect(cell of the first layer menu) for the 2nd layer menu data validation source.