Dependent Lists In Excel

An interesting feature of Excel is the dependent lists. This means that you can create a drop-down list in a cell (using data validation), and then, by choosing a value from that cell, you can limit the available values on another cell. The value of the last cell is actually dependent on the value of the previous one.

Step 1: First create your initial list, with names that are names of other lists. Example:

List A

List B

List C

Step 2: Then, create the lists that are included in the initial list:

List A1

List A2

Component B1

Component B2

Component B3

Component C1

Component C2

Component C3

Component C4

Step 3: Finally, create all the following lists:

Component A11

Component A12

Component A21

Component A22

Component A23

Step 4: Having created all the lists, then you should name them using the define name command:

The tricky part here is to AVOID naming the lists using the _ character. So, the List A you should name it as ListA (one word).

Step 5: Choose the cell in which you want to have the initial list, for example, C10:

Step 6: Chose the next cell, the value of which will depend on the value of the previous cell, for example, E10: 

Step 7: Continue with the subsequent lists, for instance in cell G10:

The substitute function is used to delete the spaces between the words since the lists were named using one word. So, the substitute function converts the value List A to ListA (one word), which is also the name of the list.

I have created an Excel workbook to help you understanding dependent lists. The lists are related to hydroelectric plants. You can download the workbook in the downloads section that follows.

---

Downloads

---