Cascading List with Formula
This article shows you how to set up a cascading drop down menu with formulas. Creating data validation lists which are linked to one another with Excel formula is a handy technique to know. Cascading data validation lists are helpful when using categories and sub-categories which show the relationship between the category selected and its sub category. So when you select a value from the first list, only the values related to that list are shown in the second list. These type of set ups are relatively straight forward for a two validation list. It becomes more complex for 3 or more. The Excel VBA article Cascading Drop Downs in Excel might be of help as it takes the concept one step further. I have seen this method achieved with named ranges and the Indirect function. However, the following method will achieve the result with 5 named ranges and this will suffice no matter how long the first list is. Here is a picture to show what a cascading drop down is made up of.
In Column A you have your list of Managers. In Columns B to F you have those managers and the staff that work under them. So from the following picture.
When you choose a manager on left in blue, only the managers displayed under their name are available in the data validation list.
To recreate the above 2 level dynamic drop down list with formula you will need the following formula. The names at the top (Header for example) are the names I have given to each named range.
Header Excel formula:
FirstRow Excel formula:
MatchColum Excel formula
CurEntryCount Excel formula
CurList Excel formula
Attached is an Excel example of a cascading lists with formula.