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:
=INDIRECT(A2&”_colours”)
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).