Home > Blog

How to make many levels of dependent drop-down boxes

In the blog Excel – How To Make A Drop-Down Dependent On Another Drop-Down, I showed how to make the contents of a drop-down box dependent on the contents of another cell or drop-down box.  Whilst this example provides “only” one level of dependencies, it is simple to provide three, four or more dependent drop-down boxes.

To do this, you will need to use a standard format for the naming convention so that the triggering term is included with a standard suffix.  In the example below, we have car makes and models and car makes and standard colours.

The same method of INDIRECT is used; however, now we concatenate the suffix to the car make:



Data structure for multiple dynamic dependent drop -down boxes

You will notice that this example has dynamic ranges using the table method described in the blog post How To Make A Dynamic Drop-Down Box.

Now the individual ranges are dynamic.  If you add a new Make, you will need to add two new tables for the Models (titled with the make) and the standard colours (titled with Make_colours).

Miles Goodchild

Miles Goodchild has been a Program and Programme Planner, PMO lead and Project Manger since 1998.  He enjoys using MS Office, especially Project, Excel and Visio to make life easier and simpler.  In the course of this he created SummaryPro.  In this blog he shares some of the tips and techniques he has learnt over the years in the hope that they will be useful to you.