### 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

`=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))`

But how about validating the colors.. Let's take 2 steps, firstly lets make a list a of valid colors of any sort

Its dynamic named range - colors - would be this

`=OFFSET(colors!\$A\$1,1,0,COUNTA(colors!\$A:\$A)-1)`

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)))))`