copy

Friday, 8 July 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.

Did you like this post? If yes, then share it with your friends. Thank you!



Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info