Microsoft Office 2010 Product Key Creating Dynamic

Today’s author, Dany Hoter,Microsoft Office 2010 Professional, a Product Planner for the Excel team,Microsoft Office 2010 Product Key, 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,Microsoft Office Professional Plus 2010, product categories and sub-categories,Microsoft Office 2010 Product Key, class and student name,Microsoft Office 2007 Enterprise, 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: In Step 2 named ranges are created automatically based on a selected range.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.The validation rules are propagated automatically to each new row in the table.The lists of regions and countries do not have to be in the same worksheet as the input cells. <div