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:
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 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:
The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.