Forum Discussion
Dynamic dropdowns for multiple levels
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:
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.
| Dropdown level 1 | Dropdown level 2 | Dropdown level 3 |
| Group 1a | Fruit 1 | Vegetable 1 |
| Group 1a | Fruit 2 | |
| Group 1a | Fruit 3 | Vegetable 2 |
| Group 1a | Fruit 3 | Vegetable 3 |
| Group 1b | Box A | Letter 1 |
| Group 1b | Box B | |
| Group 1c | Example 1 | |
| Group 1c | Example 2 | |
| Group 1c | ||
| Group 1c | ||
| Group 1d | Example 5 | |
| Group 1d | Example 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 1 | Column 2 | Column 3 |
| Group 1c | ||
| Group 1d | Example 6 | |
| Group 1a | Fruit 1 | Vegetable 1 |
| Group 1a | Fruit 2 | |
| Group 1c | Example 2 |
Many thanks in advance!
1 Reply
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.
