This allows a user to select a city from a short list of options by default, but also provides an easy way to view and select a city from a longer list of cities. Note: I ran into this formula and approach on the excellent Chandoo site. In this formula, the IF function is configured to test the value in cell C4. When C4 is empty or contains any value except “See full list”, the user sees a short list of cities, provided in the named range short_list (E6:E13):
If the value in C4 is “See full list”, the user sees the long list of cities, provided in the named range long_list (G6:G35):
The named ranges used in the formula are not required, but they make the formula a lot easier to read and understand. If you are new to named ranges, this page provides a good overview.
Dependent dropdown lists
Expanding on the example above, you can create multiple dependent dropdown lists. For example, a user selects an item type of “fruit”, so they next see a list of fruits to select. If they first select “vegetable” they then see a list of vegetables. Click the image below for instructions and examples:
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
