Dynamic Dependent Data Validation Lists

Roger Govier has created a sample workbook and instructions that show you how to create dynamic dependent data validation lists. His technique lets you create multiple levels of dependent data validation, without defining a named range for each list. Instead, Roger's formula uses the INDEX function, with MATCH, to find a column heading, then shows the list from that column.

For example, on one sheet you can list regions, countries, areas and cities.

Then, on another sheet, select a region in column A, and see only the related countries in column B's data validation drop down. In column C, you'd see only the areas in the selected column, then only the applicable cities in column D.

How It Works

With Roger's technique, you'll create four defined ranges, then use two of those ranges as the source for data validation drop down lists. The ranges are dynamic, so you can add more items to any list, or add new lists, and the defined ranges will automatically adjust.

There are full instructions for Data Validation – Dynamic Dependent Lists, and a sample workbook that you can download. Also, Roger Govier's contact information is at the bottom of that page.

____________________