Dependent Lists In Excel

Share this

July 8, 2011

Last updated: 30/09/2017, 1 min read

Dependent Lists In Excel


Introduction


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.


How to do it


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

Initial List
List A
List B
List C
Step 2: Then, create the lists that are included in the initial list:

List A
List A1
List A2
List B
Component B1
Component B2
Component B3
List C
Component C1
Component C2
Component C3
Component C4
Step 3: Finally, create all the following lists:

List A1
Component A11
Component A12
List A2
Component A21
Component A22
Component A23

Step 4: Having created all the lists, then you should name them using the define name command:
Select the list’s components → Tab: Formulas → Define Name → Name: ListA
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:
Tab: Data → Data Validation → Allow: Lists → Source: =InitialList
Step 6: Chose the next cell, the value of which will depend on the value of the previous cell, for example, E10: 
Select E10 cell Tab: Data → Data Validation → Allow: Lists → 
Source: =INDIRECT(SUBSTITUTE(C10;” “;””))


Step 7: Continue with the subsequent lists, for instance in cell G10:
Select G10 cell Tab: Data → Data Validation → Allow: Lists → 
Source: =INDIRECT(SUBSTITUTE(E10;” “;””))   
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


Download

The file can be opened with Excel 2007 or newer.

Page last modified: January 6, 2019

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

Christos E. Samaras

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Add Content Block
>