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.
Problems With INDIRECT
For example, in the workbook shown below, there is a dynamic range named NumList, with the formula:
You can SUM the dynamic named range:
for the correct result of 10.
However, if you use the INDIRECT function, the result is a #REF! error.
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).
Then, refer to those named ranges in the dependent data validation formula.
You can see this example, and a formula that substitutes invalid characters, on the Contextures website, dependent data validation page.