Data validation with conditional list

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.

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)

Data validation with conditional list set up

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:

Data validation with default short list

When the user selects "See full list", they can select from the longer list of cities in G6:G35:

Data validation with optional long list

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: