Quick Links
What is a dropdown list?
Dropdown lists allow users to select a value from a predefined list.
This makes it easy for users to enter only data that meets requirements.
Dropdown lists are implemented as a special kind of data validation.
The screen below shows a simple example.
In other words, how can you make a dropdown list dynamic?
They are created with data validation, using a custom formula based on theINDIRECT functionandnamed ranges.
Read on to see how to create dependent dropdown lists in Excel.
INDIRECT accepts text values and tries to evaluate them as cell references.
Why complicate a nice simple formula with INDIRECT?
INDIRECT maps text to a named range, which is then resolved to a valid reference.
Create the lists you need.
In the example, create a list of fruits, nuts, and vegetables in a worksheet.
Test the dropdown lists to verify they dynamically respond to values in column B.
Note: the approach we are taking here is not case-sensitive.
If the text contains no spaces, SUBSTITUTE has no effect.
Practice file
The example file is attached below check it out to see how it works.
If you want to learn the technique yourself, I’d recommend you build a file of your own.
The best way to learn is by doing.
