Assuming dataset looks like
Country Region Product
UK North fdhlw46
UK North fdhlw47
UK North fdhlw48
UK North fdhlw49
UK North fdhlw50
UK South fdhlw51
UK South fdhlw52
UK South fdhlw53
UK South fdhlw54
UK South fdhlw55
UK South fdhlw56
UK South fdhlw57
UK West fdhlw58
UK West fdhlw59
UK West fdhlw60
UK London fdhlw61
UK London fdhlw62
USA New York fdhlw63
USA New York fdhlw64
USA New York fdhlw65
USA New York fdhlw66
USA Chicago fdhlw67
USA Chicago fdhlw68
USA Chicago fdhlw69
USA Chicago fdhlw70
USA LA fdhlw71
USA LA fdhlw72
USA LA fdhlw73
USA LA fdhlw74
USA LA fdhlw75
How can I get cascading dropdowns/picklists in excel where the users selects, the country, which then narrows down to region and finally product?
So far all I have is a range called 'country' which I can then "data - validation - source: = country", but this only gives me the first value, and it doesn't strip duplicates
http://www.contextures.com/xlDataVal13.html
This approach does mean defining a unique set of values for each grouping, but this is trivial.
Thanks for the other ideas.
Select the range then Data -> Filter -> Autofilter. Does that achieve what you're looking for?
I think you want to use the INDIRECT function to lookup the value of a cell, rather than define a lookup range directly in the data validation. Have a look at this page which explains it fairly well.
Be warned though that multiple cascading validations like this have particular problems of their own. Chiefly, if you make a second (or third etc.) selection, then go back and edit the first selection, there is no automatic flag to say that the selection may now be invalid. For example, if you have the following:
Type Detail
Fruit Apple
...and then change the first column:
Type Detail
Vegetable Apple
...then you may now have a problem. Finding such inconsistencies can be very hard, or requires some code to highlight/fix. The DDoE blog shows one method to highlight such errors using conditional formatting, but this is very calculation intensive and doesn't scale well.
Be aware that multiple lookups of this nature can bring a workbook to its knees if used liberally, even without using conditional formatting.
All in all, for a small project where data entry is tightly controlled this is a flexible method of cascading drop down lists, but if you have little control over the users, or if the data collected will grow past a few hundreds lines then you'll probably want to look at validating the validation (!) using VBA, or just going the whole hog and forcing entry through a userform, which is by far the best option.
Edit: Example workbook
Edit again: If the data absolutely has to stay the way it is, then an advanced filter which creates the named ranges used in the validation would be required. At this level of complexity though you'd surely be better off just using a form for data entry.