Cascading List with Formula
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
=List!$A$1:INDEX(List!$1:$1,MATCH(REPT("z",255),List!$1:$1))
FirstRow Excel formula
=ROW(List1!$A$1)
MatchColum Excel formula
=MATCH(Data!$A19,Header,0)
CurEntryCount Excel formula
=COUNTA(INDEX(List!$A:$GY,FirstRow+1,MatchColumn):INDEX(List!$A:$GY,65536,MatchColumn))
CurList Excel formula
=INDEX(List!$A:$GY,FirstRow+1,MatchColumn):INDEX(List!$A:$GY,FirstRow+CurrEntryCount,MatchColumn)
Attached is an Excel example of a cascading lists with formula.