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
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.
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.
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.
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
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.
what does the list of valid colors look like for this combination ?
does what was entered appear in that list?
For help and more information join our forum, follow the blog, follow me on twitter
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'Reilly, Amazon and all good bookshops. You can also read a preview on O'Reilly
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Organizing named ranges >