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

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

For help and more information join our forumfollow the blogfollow me on twitter

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, All formats are available from O'ReillyAmazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.



Comments