Limit the choices in an Excel drop down list, depending on the value in another cell.
For example, select "Fruit" in cell B2, and "apple, banana, peach" appear in the drop down list in cell C2. In this dependent data validation example, if a country is selected from the first drop down, only that country's cities appear in the next column's drop down list. After you learn the basics, you can use the Excel IF function to make the selection more flexible.
If no country is selected, the City column will show a list of world cities in the dependent drop down list.
In this example, the first list will be named Produce.
It contains the Produce categories -- Fruit and Vegetable.
NOTE: If a Produce Type has not been selected, the Item drop down in that row will not work.
If you need to, you can add another set of dependent data validation dropdown lists that depend on the selections in the first two dropdowns.
For example, select a country and region, then select a city in that region.
=INDIRECT(SUBSTITUTE(B2&C2," ","")) In some workbooks, you might need to have two-word items in the first Excel data validation drop-down list.
For example, your choices are 'Red Fruit', 'Green Fruit' and 'Yellow Fruit' =INDIRECT(SUBSTITUTE(A2," ","")) You may need items in the first Excel data validation drop-down list that contain characters not allowed in range names, such as the ampersand (&).
For example, your choices are 'Red Fruit', 'Green Fruit' and 'Yellow & Orange Fruit'.
For the dependent lists, you can create ranges with one-word names, such as YOFruit.
Then, create a lookup table, which lists each item in the first Excel data validation drop-down list, and the range where its dependent items will be stored.
To start, create the item lists and the first Excel data validation drop-down: With Red Fruit selected in cell A2, the VLookup formula will return Red Fruit List as the range name for the dependent list.