Creating Dynamic Validation Ranges

Today’s author, Dany Hoter, a Product Planner for the Excel team, shares a neat trick he learned recently for creating dynamic data validation ranges.  The sample file used for this blog post can be found in the attachments at the bottom of this post.

The problem: Validating data entry based on hierarchical (parent child) data. The example used is regions and countries but it could be countries and cities, product categories and sub-categories, class and student name, etc.

You want to enter a region from a list of regions and in the next cell you want to select a country but only from the countries which belong to that region.

How do you define the list of countries to validate against? The trick is basing the country validation list on an expression which will point to a different range based on the region value.

Follow the instructions in the file itself (attached below) and see how it works.

Points to notice:

1. In Step 2 named ranges are created automatically based on a selected range.


2. The named range used for country validation uses relative reference to point to the list of values. In this way the same mechanism will work anywhere in the spreadsheet. The validation of the cell with country is based on the region value to the left of country. The actual expression is =INDIRECT(B13). For example the named range for countries in Europe is Europe. The indirect function uses the value of the region to point the relevant named range. B13 happens to be the cell to the left of the current cell when defining the name.


3. The validation rules are propagated automatically to each new row in the table.


4. The lists of regions and countries do not have to be in the same worksheet as the input cells.