Search Contextures Sites

Excel -- Data Validation -- Create Dependent Lists		Download a sample file
Create Named Lists Apply the Data Validation Test the Data Validation Adding a Third Dependent List	Using Two-Word Items Using Items with Illegal Characters Using Dynamic Lists

You can limit the choices in a Data Validation list, by using named ranges and the INDIRECT function. In this example, if Fruit is selected as the Category, only Fruit appears in the Item drop-down list. Another method is to use the OFFSET function, to extract items from a sorted list, as described here: Dependent Dropdowns from a Sorted List

Create Named Lists Start by creating Named Lists, which will be the choices in the Data Validation cells. In this example, the first list will be named Produce. It contains the Produce categories -- Fruit and Vegetable.
1. Create the first Named List In an empty area of the workbook, type the entries you want to see in the drop-down list. These should be one-word entries, to match the dependent list names that will be created. (If you need to use multiple word entries, see the instructions here.) Select the cells in the list (but not the heading). Click in the Name box, to the left of the formula bar Type a one-word name for the list, e.g. Produce. Press the Enter key.
2. Create the supporting Named Lists Type the entries you want to see in the drop-down list for one of the Produce categories. Select the cells in the list. Click in the Name box, to the left of the formula bar Type a one-word name for the list, e.g. Fruit. This name must be exactly the same as the matching entry in the Produce list. Press the Enter key.

Create another list with the items for the next category -- Vegetable in this example.

Apply the Data Validation The cells in the Category column will allow a List. The cells in the Item column will use the INDIRECT function to select a list..

1. Apply the Data Validation a) Select the cells in which you want to apply data validation using the Category List b) From the Data menu, choose Validation.

c) From the Allow drop-down list, choose List d) In the Source box, type an equal sign and the list name, for example: =Produce e) Click OK.

2. Create the Dependent Data Validation Select the cells in which you want to apply data validation using the Fruit or Vegetable List, dependent on which Category has been selected From the Data menu, choose Validation. From the Allow drop-down list, choose List In the Source box, type an equal sign and INDIRECT function, referring to the first data cell in the Category column: =INDIRECT(A2) Click OK.

Note: If cell A2 is empty, you'll see the message shown at right. Click Yes to continue

Test the Data Validation Cells in the Category column will display items in the Produce List. Cells in the Item column will show items from the Fruit or Vegetable List, depending which has been selected in the Category column

Adding a Third Dependent List You could add another set of 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.

Create the two named ranges and dropdown lists as described above. Create another set of named ranges, naming them for the available combinations in the first two dropdowns. For example, you might create ranges named CanadaOntario and USANewYork. For the third dropdown, choose to Allow: List, and use a formula that combines the entries in the first two columns, and removes the spaces from the names. For example, in cell D2, the data validation formula would be:   =INDIRECT(SUBSTITUTE(B2&C2," ",""))

Using Two-Word Items You may need to have two-word items in the first drop-down list. For example, your choices are 'Red Fruit', 'Green Fruit' and 'Yellow Fruit' Create the first named range and dropdown list as described above. Create the supporting named lists, using one-word names, e.g. RedFruit, GreenFruit, YellowFruit For the second dropdown, choose to Allow: List, and use a formula that removes the spaces from the names. For example:   =INDIRECT(SUBSTITUTE(A2," ",""))

Using Items with Illegal Characters You may need items in the first 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, you can create a lookup table, which lists each item in the first drop-down list, and the range where its dependent items will be stored. To start, create the item lists and the first drop-down: Create the first named range and drop-down list as described above. In this example, the range is named FruitList, with values in cells A6:A8. The drop-down list in cell A2 uses FruitList as its source. Create the dependent lists, and name them, using one-word names, e.g. RedFruit, GreenFruit, YOFruit. In this example, RedFruit is in A11:A12, YOFruit is in B11:B12 and GreenFruit is in C11:C12. Select an item from the drop-down list in cell A2. Next, you'll create the lookup table, to match each item with its dependent items' range name. In the column to the right of the FruitList range, enter the range name for each item's dependent list. For example, YOFruit is entered as the dependent range name for Yellow & Orange Fruit. Name the lookup table. In this example, the range A6:B8 is named NameLookup. Select cell B2, and from the Data menu, choose Validation. Choose to Allow: List. For the Source, enter a formula that uses a VLookup formula to find the dependent list's range name. For example:   =INDIRECT(VLOOKUP(A2,NameLookup,2,0)) With Green Fruit selected in cell A2, the VLookup formula will return GreenFruit as the range name for the dependent list. The GreenFruit list will be displayed in cell B2's drop-down.

Using Dynamic Lists Because the INDIRECT function only works with references, not formulas, the previous method won't work with dynamic lists. Instead, you can use the following method: Create the first named range and dropdown list as described above. Create the supporting named lists, and name the first cell in each range, e.g. cell B1 is named Fruit and cell C1 is named Vegetables. Name the column in which each list is located, e.g. column B is named FruitCol and column C is named VegetablesCol For the second dropdown, choose to Allow: List, and use a formula that calculates the lookup range. For example, if the first dropdown list is in cell E2: =OFFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT(E2&"Col")),1) if two-word items will be used, you can include the SUBSTITUTE function in the formula:

Download a sample file for Dynamic Lists

=OFFSET(INDIRECT(SUBSTITUTE($F2," ","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($F2," ","")&"Col")),1)
Download a sample file for Dynamic Lists

1. Data Validation 2. Data Validation -- Create Dependent Lists 3. Hide Previously Used Items in a Dropdown List 4. Display Messages to the User 5. Use a List from Another Workbook 6. Validation Criteria Examples 7. Custom Validation Criteria Examples 8. Data Validation Tips 9. Data Validation Documentation    10 Data Validation -- Combo box       11. Data Validation -- Combo Box - Named Ranges 12. Data Validation -- Display Input Messages in a Text Box  13. Data Validation -- Dependent Dropdowns from a Sorted List     14. Data Validation -- Combo Box -- Click 15. Data Validation -- Create Dependent Lists With INDEX

 

Home     Excel Tips     Excel Files      Blog    Contact
Privacy Policy		The Excel Store	Debra Dalgleish