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
Validating the modelThis is pretty straightforward. Our dynamic named range – Models – looks like this
Validating the make
Our dynamic named range -Makes- looks like this.
Validating the color.
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.
Using ISNUMBER,SEARCH,MATCH,INDEX and DYNAMIC RANGES to validate data
So we are going to need to enter a custom formula in the data validation dialogue for column C.
- Validate the color entered is a real color
- Validate the color is valid for the make Model Combination
is the color known?
what does the list of valid colors look like for this combination ?
does what was entered appear in that list?
putting it all together as a custom formula in the dialog box