Normally, where the values in the column of a lookup array are unique there is only a need to match the value in the last dynamic data validated list with the value in the relevant column of the lookup array to provide the range of values for the next dynamic data validated list. However, where values in a column are not unique, is there a way to create dynamic data validated lists in Excel? I assume that this could be achieved by ensuring the values in more than one column must have been selected in order to provide the dynamic range for the third, e.g. X must have been selected in the first drop down and Y in the second, in order to lookup the values for the third dynamic data validated list, but I can't work out how.
As an example let's say that my lookup array looks like this:
Field1,Field2,Field3 A,C,F A,C,G A,D,H B,E,I B,C,J B,C,K
If I select B in the first dynamic data validated list and C in the second, I would want the range for the third to be J and K, not F, G, J and K.
Best How To :
You'd have to setup a second column for
Field2 and for
If you assume your dropdowns are located in cell
C10 and your fields are in column A-C then you could go over to column D and make
D2 would be
if(countif(d$1:d1,if($a$10=a2,b2,""))=1,"",if($a$10=a2,b2,"")). You could drag D2 down for as many rows as you need. Once you do that you name that range to be
Field2 (or whatever you're using as the name in your data validation list).
Field3 you basically do the same thing except you use
and. So in
E1 you'd do
E2 would be
=if(countif(e$1:e1,if(and($a$10=a1,$b$10=b2),c2,""))=1,"",if(and($a$10=a1,$b$10=b2),c2,"")). When you do that, you name it
The downside is that the dropdown will have blanks and changing one of the first 2 won't reset the last ones. To overcome this pitfall you'd have to setup a worksheet change event in VBA.
OK start from scratch... I'm putting the dropdowns in A12-C12 now.
You still have essentially formulas in column D and E but then you have to make 3 more columns to uniqueify (that's a technical term meaning to make unique) the previous columns. You can't see column H in this pic but it's the same template for
field3. Those are array formulas so you don't hit enter after you've typed it in you hit CTRL-SHIFT-ENTER. You'll know you've done it right because it'll put curly braces around the formula. Once you make the formula copy it down.
Once you've done that then go to formulas define name.
Once there you fill out the name like this
Make sure you change the row in the
countif to match your data but for the first argument of
offset you just pick the first cell in the list and keep those two 0s as they are.
Once you've done that you make the dropdown based on the name from the previous step.
This should get you to about 99% of where you want to be. Unfortunately you are still susceptible to 1 blank if the first row is blank in the unique columns. I couldn't figure out how to get rid of it but since it's only 1 blank it shouldn't be too bad. Also, if you change a parent dropdown it won't reset the child dropdowns.