Other Links
This webpage is an extract of the
Tutorial on Excel
from the basic to the extraordinary
The most useful and powerful tools in Excel
The drop-down lists in Excel
Lesson 2-2: Excel Drop-Down Lists and Data Validation
(Level: Beginner, Intermediate and Advanced)
Part 4:
Cascading Drop-Down Lists
When selecting a type of wood for a floor in a first drop-down list one expects only the colors available for the type of wood selected to show in the second drop-down list not all the colors of all the types of wood. It is called a cascading drop-down list
To achieve such a thing you first submit the list for the first DDlist in its source box. For the second DDList you will submit a formula like the following:
=IF(G4="Maple",A1:A12,B1:B12)
meaning that if the value in cell G4 is "Maple" then the second DDlist should feed on range A1 to A12 else it should feed on B1 to B12.
You can also work with named fields if your lists are maintained on another sheet. The formula will then look like this:
=IF(G4="Maple",nfMaple,nfOther)
This approach works if you have one to eight different lists to chose from because you cannot nest more than 7 IF's. The formula would look like this:
=IF(G4="Maple",nfMaple,IF(G4="Ash",nfAsh,IF(G4="Oak",nfOak,IF(G4="Pine",nfPine,
IF(G4="Spruce",nfSpruce,IF(G4="Cypress",nfCYpress,IF(G4="Cherry",nfCherry,nfOther)))))))
If you have more than 8 secondary lists you need to create a dynamic list on another sheet using INDEX/MATCH formulas. See the easy way to do it in "excel-tutorial-ddlists.xls".
In Part 1 you will discovered how easy it is to create a drop down list in Excel
In Part 2 you will discover how to use a range of cells as Excel drop down list
In Part 3 you will discover how to assemble all your lists on a single sheet to maintain them easily and how you can refer to a list on another sheet to feed a drop down list. (In the downloadable tutorial)
In part 4 you will discover how to create cascading drop down list where the values offer in the second drop down list depends on what has been selected in the first one. (In the downloadable tutorial)
In Part 5 you will discover how to modify, extend and delete your drop down lists
In Part 6 you will discover different applications that you can develop with Excel drop down lists including a report that completely changes when you select a value in the drop down lists
This webpage is an extract of the
Tutorial on Excel
from the basic to the extraordinary
The most useful and powerful tools in Excel