Using Named ranges to do 3d validation
Let’s say that you want to collect data like this on a data entry sheet.

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 model

This is pretty straightforward. Our dynamic named range – Models – looks like this


and can be applied as a data Validation list to the Make Column (A) of the data entry sheet.

Validating the make

Our dynamic named range -Makes- looks like this.

and can be applied as a data Validation list to the Type Column (B) of the data entry sheet.

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.

So we could easily apply this as a data validation list against column C, but it wouldn’t check for valid combinations. We have to do something more complex.


So we are going to need to enter a custom formula in the data validation dialogue for column C.

    1. Validate the color entered is a real color
    2. 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

=AND(ISNUMBER(MATCH(C2,colors,0)), ISNUMBER(SEARCH(C2,INDEX(colorTable,MATCH(A2,Makes,0),MATCH(B2,Models,0)))))