Validating the Validation (Data Dependent Drop Down Lists)

You could use something like the below in cell D4.

Formula:

=IF(COUNTIF(INDIRECT(C2),C4)=0,"Error","")

It will check if the value is a valid entry for the specific data validation list and show "Error" if not.

Not a perfect solution, but may prompt the user to fix the error of their ways.

BSB

You could use a formula like this in C6:

=IFERROR(ISNUMBER(MATCH(C4,INDIRECT(C2),0)),FALSE)

This will return TRUE or FALSE.

Hope this helps.

Pete

Try

=IFERROR(MATCH($C$2,Position,0),"Invalid Input")

will error if Position not found in "Position" table

Thanks all. How would the formula change if I added another validation layer into the data?

NB - I've uploaded an amended workbook to reflect this.

Try

=IFERROR(AND(MATCH($C$2,Position,0),MATCH($C$6,Height,0)),"Invalid Input")

I created named range "Height". If there "Heights" depending on position, then it gets more complex

Thanks John. I've incorporated the AND function as you suggested and the formula below seems to work based on my example. Presumably now I just need to adapt it to fit my actual data and incorporate multiple MATCH criteria for each level.

=ISNUMBER(--AND(
MATCH(C4,INDIRECT(C2),0),
MATCH(C6,INDIRECT(SUBSTITUTE(C4," ","_")),0)
))

Try it! depending on the complexity VBA might be an option (WorkSheet_Change).