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

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:
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.