Dynamic dropdowns for multiple levels

Occasional Visitor

Hi, please can someone help me with dynamic dropdowns? I have the latest version of Excel so can use UNIQUE and FILTER formulas if required.

 
My source data (sample) is on a hidden tab and looks like this:
Dropdown level 1Dropdown level 2Dropdown level 3
Group 1aFruit 1Vegetable 1
Group 1aFruit 2 
Group 1aFruit 3Vegetable 2
Group 1aFruit 3Vegetable 3
Group 1bBox ALetter 1
Group 1bBox B 
Group 1cExample 1 
Group 1cExample 2 
Group 1c  
Group 1c  
Group 1dExample 5 
Group 1dExample 6 


On a separate tab (see table below as a random example), I would like the user to be able to select the first dropdown in the first column and then the dropdowns in column 2 and column 3 will be dependent on what was select in the previous dropdowns. I have figured out how to do the first column using the unique function however I am struggling with column 2 and column 3 as it needs to be dynamic so that if a new row is added it should update automatically the dropdown.

Column 1Column 2Column 3
Group 1c  
Group 1dExample 6 
Group 1aFruit 1Vegetable 1
Group 1aFruit 2 
Group 1cExample 2
 

Many thanks in advance!

1 Reply

@ExcelAlteryx 

 

See if what I've created in the "Three Levels" tab of this workbook meets your need. I know you can add rows to the basic table in the top left region, and the resulting values will all populate the corresponding dynamic drop-down lists. Mine is abstraction in the extreme, to illustrate the concept. I may take it (created in response to your question) and go apply it to my budget spreadsheet, where I took a different approach. But I'll be interested in whether this is what you are looking for.