And you want to validate that a valid color is entered for the combination of make/type in addition to validating the entries for Make and Model.
Firstly, create a table for validation that looks like this
=OFFSET(Lookups!$A$1,1,0,COUNTA(Lookups!$A:$A)-1)
Our dynamic named range -Makes- looks like this.
=OFFSET(Lookups!$A$1,1,0,COUNTA(Lookups!$A:$A)-1)
Suddenly it’s got much more complex. We need a named range the shape of the table, and worse, each table entry can contain multiple valid colors.
The dynamic named range – colorTable – for the table body can be defined quite easily from the make and model range.
=OFFSET(Makes,0,1,ROWS(Makes),COLUMNS(Models))
So we are going to need to enter a custom formula in the data validation dialogue for column C.
is the color known?
=ISNUMBER(MATCH(C2,colors,0))
what does the list of valid colors look like for this combination ?
=INDEX(colorTable,MATCH(A2,Makes,0),MATCH(B2,Models,0))
does what was entered appear in that list?
=ISNUMBER(SEARCH(C2,INDEX(colorTable,MATCH(A2,Makes,0),MATCH(B2,Models,0))))
putting it all together as a custom formula in the dialog box
=AND(ISNUMBER(MATCH(C2,colors,0)), ISNUMBER(SEARCH(C2,INDEX(colorTable,MATCH(A2,Makes,0),MATCH(B2,Models,0)))))
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines