We've looked at how to use blister lists to lookup data in Using blister custom functions. Now lets see some examples of using these lists to validate data entry.
The simplest kind of list validation is simply to ensure that data entered in a cell is a member of a list. We can apply a blister list (local, group or global) to a cell to ensure valid data entry. In the case below, the ISO code will be entered and the Country and currency will be looked up. We'll cover the code for applying validations later, but here's what the generated drop down list looks like.
We'd finish up the sheet by adding blisterLookups() based on the code entered. They would look something like this.
And give this result
So far so good, but where things really get interesting is when we apply dependent or dynamic validation. The objective here is to dynamically change the list of valid values depending on the data selected for related columns. A common requirement, but one which is very difficult in Excel and impossible in GAS using normal built in data validation techniques.
For this example we'll go back to the car list data used in Using blister custom functions. This represents the 'stock list' for a car dealer. We want to only allow the entry of the combination of models, makes and colors that are in stock. To do this we'll maintain a shared blister list of all stock- it looks like this.
Let's say we want to create a data entry sheet that only allows valid combinations from the first three columns. So if 'mercedes' is entered as the make, only sl300 or sl500 can be entered as the model, and depending on the make entered, the color needs to be validly selected. So a data entry sessions would look like this. Note how the color drop down has been limited based on the make and model entered in this row. This is called a 'dependent' validation, and is the default behavior for multicolumn validations from blister lists. The onEdit event will dynamically update the choices based on the values entered in previous lines.
All comments, suggestions, assistance, good lists are welcome as I develop this capability. You can get me on Google plus, Twitter or this forum.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Things that have been deprecated > GAS lists and validation >