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.

April 14, 2025 · 1 min · 79 words · Sarah Greene

Table of Contents

Explanation

Data validation rules are triggered when a user adds or changes a cell value.

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.

Data validation with conditional list set up

If you are new to named ranges,this page provides a good overview.

If they first select “vegetable” they then see a list of vegetables.

More than one condition can be tested by nesting IF functions.

Data validation with conditional list set up

Data validation with conditional list set up

Data validation with conditional list set up

Data validation with conditional list set up

Data validation with default short list

Data validation with optional long list

dependent dropdown list example

Excel IF function

Data validation with default short list

Data validation with optional long list

dependent dropdown list example