Data Validation serves two purposes; one, it enforces rules on what can be entered in a cell, and two, it is a convenient way to deliver feedback to the user. Several types of conditions can be applied, such as a number or date range, text length, or list. With list you can reference a range on a sheet for allowed values, or type them in, separated by commas. The rule can then offer an in-cell drop-down list of the valid selections to pick from.
User Messages
Two kinds of feedback can be given. Input Message can be used with or without a validation rule to raise a small popup of instructional text upon selecting the cell. It is similar to a cell note or comment, but does not put a corner flag in the cell, and is opened by selecting the cell rather than by hovering over it. It’s a matter of choice which kind of message to use. I like Input Messages because they don’t flag the cell, they support keyboard navigation, and they can include rules.
Because they don’t flag the cell, I use Input Messages in obvious places like column headers, or a cell marked with a symbol or made to look like a button. They’re limited to 256 characters, so they need to be concise. I’ve also put them on entire columns. This might be a bit intrusive for some users, but it makes instructions hard to miss. I generally use them on all input cells, help-symbol cells, and on either full table columns or column headers.
The message box has a bold header and a normal font body, with no formatting options. The header is a single line, up to 32 characters, and the body can have multiple lines, total 256 characters. If text wraps in an undesirable way, you can stretch the width a bit by adding spaces to the header, with a character at the end like a colon, to fill up the 32 characters allowed. You can use the body with no header for a non-bold message. To use the header with no body for a bold message, enter a single space in the body.
The second type of feedback is Error Alert. It requires a validation rule and raises an error popup when an invalid value is entered. Once you’ve set a rule, the user will get a message if they don’t follow it, unless you uncheck the box to show the alert (and then, what’s the point of the rule?). You can define three levels of error.
If you leave on the Show setting and don’t give them a message, Excel will give them a generic one, and the default is Stop style. When setting rules it is good practice to give messages that tell what valid entries are, rather than simply reject invalid ones with a “guess again!” taunt. For example, I’ll give the title “Oops!” and the message “Please pick or type a member of the drop-down list”, or “Please enter a number between 1 and 4”.
Validation Criteria
When the validation criterion is Any value, it means that error checking is turned off. Otherwise, criteria are set to define what types and ranges of data are allowed. There are seven data types: Whole number, Decimal, List, Date, Time, Text length, and Custom.
Numeric Criteria
All except List and Custom are numeric criteria that use the same settings; the data type, the inequality expression, and the boundaries. The inequalities are: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to. The boundaries are an upper and lower limit for between or not between, or a single limit for the other inequalities.
The limits can be typed in, or can refer to a cell, or can be calculated. For example, you could allow a date between =TODAY()-365 and =TODAY(), with an error message, “enter a date within the past year”. To allow 0% to 100%, you would select decimal between 0 and 1.
List Criteria
Selecting the List data type disables the inequality selector, prompts for the list Source, and enables In-cell dropdown as a checkbox option. Source can be a comma-separated list, numeric or text with no quotation marks, or a cell range or name.
The section on function choice presents a case where a Data Validation picklist is used to select from a list of inputs, which selection is then used to derive one from a list of outputs. Attempting to set up the pick list and the conditional formulas reveals a dual maintenance risk.
Whether you use nested IFs, comparing the selection with each input value as a condition, or CHOOSE with MATCH of the selection against a list, the list of possible values in the formula need to match the values in the Data Validation list source.
In the formula you can use either an array constant or a table column reference, but Data Validation does not accept either of these as the list source, so you would need to enter the same list there as well. The best way to avoid dual maintenance then is to use a sheet range to hold the list, which can be referenced in both formulas and Data Validation.
The list range must be one-dimensional and can be vertical or horizontal, normally vertical. It doesn’t require a table or a range name, but I recommend both. By using a table column as the input source, you can add output and other columns as reference or for use in formulas. All you need for the Data Validation source is to create a name for the input column, such as List=Table[Field], then enter =List as the Source.
For a short list that’s not likely to change, dual maintenance is a reasonable option. Type the list in the Data Validation list source. For formulas, type the list, or create an array constant name if helpful.
Custom Criteria
The Custom validation type takes a formula entry. An example of this is to validate email address format, =NOT(ISERROR(SEARCH("*@*.*",C10))). Another example is in the Startup File Calendar sheet, where the Work Week string is validated by this formula that refers to the same cell it is validating: =AND(LEN(WkCustom)=7, NOT(ISERR(BIN2DEC(WkCustom)))).
By using Data Validation together with tables and Name Manager you create a transparent and sustainable configuration and governance model for the user interface.
Comments