Friday, 8 July 2011
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 you create your initial list, with names that are names of other lists. Example:
Step 2: Then, create the lists that are included in the initial list:
Step 3: Finally, create all the subsequent lists:
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 in order 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 in order to help you understanding dependent lists. The lists are related to hydroelectric plants. You can download the workbook in the downloads section that follows.
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!