You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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:
Comments: (10) Collapse
We did this few weeks ago and worked perfectly well. The main issue was trying to change the value of the 'city' cell when the 'country' item is changed for another.
We couldn't find another way to do this than VBA.
Good work, fellows!
Your attachment is an Excel 2007 file (xlsx) format. Can you make this available in .xls format for those of us who do not have Excel 2007?
Anothwer way could be using OFFSET, MATCH and INDEX formulas.
Not always optimum but useful.
1. Define DbRng as $G$3:$J$9
2.Use OFFSET(DbRng;1;MATCH(B13;INDEX(DbRng;1;);FALSE)-1;ROWS(DbRng)-1;1)
in Step 6. Datavalidation
1) Ctrl+Shift+F3 = Create Names
2) Dont create Static Names, Use Dynamic names (Index/Counta())
3) Dont use Indirect, It is volatile and does not work with Dynamic names -Use Evaluate
Hey Dany,
Really nice solution. I've done this kind of thing before, but not using =Indirect() like this, which is really clean. I wish I thought of this myself...
Sam, I don't think that being 'volatile' applies here b/c validation conditions are only evaluated when the user attempts to change a value (or use the validation drop-down list). Defined names are not volatile the way that standard formula are; in fact, unless another formula on a worksheet references a defined name, it is never calculated at all, even if the defined name references something else that is recalculated.
Mike.
If you define a dynamic Name using Offset/Counta then the name gets recalculated every time a event takes place which triggers a recalc
Index/Counta makes it non volatile.
Indirect doest not work with dynamic names (Its blind to both Index and Offset)
Hence the good old XLM Evaluate
I tried it it worked oerfectly in rows b and c but not in other rows. what is the purpose of the cells b12 and c12
I tried it it worked oerfectly in columns b and c but not in other columns. what is the purpose of the cells b12 and c12
I think having Country update upon any change in Region is important. The whole point of all this work is to limit (validate) the choice of Country based on the selection of Region. If you select a Region and Country, and then change the Region, your selection of Country is no longer valid. Your prior choice of Country should be cleared as soon as you change the Region. How can you make this happen?
I am not an expert so please be explicit.
Comments: (loading) Collapse