3d Validation

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

Page Content hide
1 Validating the model
2 Validating the make
3 Validating the color.
4 Using ISNUMBER,SEARCH,MATCH,INDEX and DYNAMIC RANGES to validate data

Validating the model

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

=OFFSET(Lookups!$A$1,1,0,COUNTA(Lookups!$A:$A)-1)
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.

=OFFSET(Lookups!$A$1,1,0,COUNTA(Lookups!$A:$A)-1)
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.

=OFFSET(Makes,0,1,ROWS(Makes),COLUMNS(Models))
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.

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.

    1. Validate the color entered is a real color
    2. Validate the color is valid for the make Model Combination

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)))))
Learn more about snippets or organizing named ranges