Dropdown type of inputs are very common in Excel models, but dependent dropdowns take it to the next level, and are dynamic. This means that available options of the dropdown menu will change based on other selections. This article will show you how to create dependent dropdowns. If you have separate lists that are static, the INDIRECT function is the easiest way to create dependent dropdowns.
Syntax
=INDIRECT(reference of source dropdown)
Steps
- Start by adding named ranges for each list
- Create a data validation dropdown for source list (i.e. $B$3:$B$5)
- Create a data validation dropdown for dependency list with the INDIRECT function (i.e. =INDIRECT($I$3))
How
The INDIRECT function returns the reference specified by a text string. This means that the INDIRECT function evaluates its string argument, and returns the cell or the range. You can use regular references like $I$3 or named ranges like Fruits. For example, =INDIRECT("Fruits") function returns {"Apple";"Grapes";"Melon";"Strawberry"} array in our example, because we already defined the range D3:D6 as Fruits.
We can return array from strings and dropdowns as dynamic ranges (array of values) to populate a list and create dependent dropdowns in Excel.
The first thing to do is defining the named ranges. To define a named range:
- Select the data range
- Enter an appropriate name into reference box near the formula bar
- Press the Enter key
We created these names with ranges:
- Fruits: D3:D6
- Vegetables: E3:E7
- Beverages: F3:F5
Then, create a dropdown that uses the source list for our dependent dropdown. To add a dropdown input into a cell:
- Select the cell to become the input
- Open Data Validation window under the DATA tab
- Select List from Allow dropdown
- Type in or select your range of reference for list items (i.e. B3:B5)
Finally; create the dependent dropdown, but this time type in =INDIRECT($I$3) into the Source section. The $I$3 reference is where we created our source dropdown. Remember to modify this section based on your model.
