Excel: Use Validation to Create Dependent Lists

Problem: I want to create two dropdown lists. The second list should be dependent on what is selected in the first one.

Strategy: You can use the INDIRECT function as the source of the second list. Follow these steps:

1. On a blank sheet, set up a list of items for the first dropdown: Writing, Science, Math, and Geography. Name the range Subjects.
2. In other columns, set up a list of choices available for each subject.
3. Name the second list Writing. It is critical that the range name for this list match the value in the original list.
4. Repeat step 3 for each item in the first list. In each case, the name of the new range must match the value in column A.

1. Set up dependent lists, each with a name from the first list.
   5. To select the subject from cell D2, select cell D2 and then select Data, Data Validation. Change the Allow box to List; in the Source box, type =Subjects.
   6. Click OK. Cell D2 will have a dropdown list of subjects.
   7. To set up the second dropdown, select cell D4 and then select Data, Data Validation. Change the Allow dropdown under Validation Criteria from Any Value to List. In the Source box, enter =INDIRECT(D2).

   Results: When you select a value in D2, the formula for the second dropdown list will automatically update. The INDIRECT function looks in D2 and hopes to find a formula there. When you select Writing in D2, the validation formula becomes =Writing. Because you cleverly set up a named range called Writing, Excel is able to populate the list.

   

2. Choose Science in D2, and the list in D4 reflects the Science list.

   When you change D2 to Math, =INDIRECT(D2) will become =Math. Again, because you have a named range called Math, Excel is able to fill in the second dropdown with geography subjects.

   

3. Change D2 to Geography and the validation list changes.