Summary
To allow a user to switch between two or more lists, you can use the IF function to test for a value and conditionally return a list of values based on the result. In the example shown, the data validation applied to C4 is:
=IF(C4="See full list",long_list,short_list)
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.
Generic formula
=IF(A1="See full list",long_list,short_list)
Explanation
Data validation rules are triggered when a user adds or changes a cell value. This formula takes advantage of this behavior to provide a clever way for the user to switch between a short list of cities and a longer list of cities. In the worksheet shown, the data validation applied to C4 looks like this:
=IF(C4="See full list",long_list,short_list)
The IF function is configured to test the value in cell C4. When C4 contains the text "See full list", IF returns the named range long_list. When C4 is empty or contains any other value IF returns the named range short_list.
Behavior
The user starts with the values in E6:E13 as seen below:
When the user selects "See full list", they can select from the longer list of cities in G6:G35:
The named ranges used in the formula are not required, but they make the formula easier to read. 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:
Data Validation Guide | Data Validation Formulas | Dependent Dropdown Lists
