Dynamic Dependent Excel Drop Downs

With dependent data validation, you can make one drop down list depend on the selection in another cell. For example, select Vegetables as a category in column B, and you’ll see a drop down list of vegetables in column C.
datavaldependdynamic01

Problems With INDIRECT

This technique uses the INDIRECT function, to return the range with the name Vegetables. It works well with a static named range, but INDIRECT doesn’t work with a dynamic range.
For example, in the workbook shown below, there is a dynamic range named NumList, with the formula:
=Admin!$A$1:INDEX(Admin!$A:$A,COUNTA(Admin!$A:$A))
You can SUM the dynamic named range:
=SUM(NumList)
for the correct result of 10.
datavaldependdynamic02
However, if you use the INDIRECT function, the result is a #REF! error.
=SUM(INDIRECT(C4))
datavaldependdynamic03

Dependent Data Validation Workaround

Instead of using dynamic ranges with dependent data validation, you can use a named starting cell (Vegetables) and named column (VegetablesCol).
datavaldependdynamic04
Then, refer to those named ranges in the dependent data validation formula.
=OFFSET(INDIRECT($B2),0,0,COUNTA(INDIRECT(B2&”Col”)),1)
You can see this example, and a formula that substitutes invalid characters, on the Contextures website, dependent data validation page.
___________