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 `Field3`

.

If you assume your dropdowns are located in cell `A10`

to `C10`

and your fields are in column A-C then you could go over to column D and make `D1`

`=if($a$10=a1,b1,"")`

and `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).

For `Field3`

you basically do the same thing except you use `and`

. So in `E1`

you'd do `=if(and($a$10=a1,$b$10=b1),c1,"")`

and `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 `Field3`

.

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.

EDIT:

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.