Data validation is one of the most useful yet underutilized features for worksheet user interface design. It allows you to ensure that most, if not all, of the inputs in your user interface are made correctly by disallowing FIGURE4-19 A custom on-sheet toolbar created with shapes
4.WORKSHEETDESIGN
input that does not match the rules you specify. Data validation can be as simple as restricting cell entries to whole numbers or as complex as restricting cell entries to items on a list whose contents are conditionally determined based on an entry made in a previous cell.
We assume you understand the basic use of data validation and instead demonstrate two of the more complex validation scenarios that can be cre-ated with this feature. Most complex data validation scenarios involve data validated lists or custom data validation formulas.
Unique Entries
If you need the user to enter only unique item names in a data entry list you can use a custom data validation formula to enforce uniqueness. First select the entire data entry area you need to validate. Next, choose Data >
Validation from the menu (Data tab > Data Validation in Excel 2007) and select the Custom option from the Allow list. The basic syntax of the for-mula you need to enter is the following:
=COUNTIF(<entire range>,<relative reference to input cell>)=1
The first argument to the COUNTIF function is a fixed reference to the entire data entry area that must contain unique entries. The second argu-ment to the COUNTIF function is a relative reference to the currently selected cell in the data input range.
If each entry is unique, the COUNTIF function evaluates to 1 and the entire formula evaluates to True, meaning the data is valid. If the COUN-TIF function locates more than one instance of an entry in the data entry area, the entire formula will evaluate to False and data validation will pre-vent that entry from being made. Figure 4-20 shows an example of this val-idation setup and Figure 4-21 shows it in action.
NOTE The enforce unique entries data validation technique described previ-ously only works correctly if the range being examined is entered into the Data Validation dialog as a hard-coded range address. If you try to use an equivalent defined name, this data validation technique will fail. This is a bug in the Excel data validation feature.
FIGURE4-20 Data validation configuration to force unique entries in a list
FIGURE4-21 Unique entries data validation in action
Cascading Lists
In this type of validation, the specific data validation list that is displayed for a cell is determined by the entry selected in a previous cell. In Figure 4-22, the data validation list for the Item column is determined by the selection in the Category column. All of the data validation lists are locat-ed in the hidden column A.
4.WORKSHEETDESIGN
FIGURE4-22 Initial setup for cascading data validation lists
FIGURE4-23 Defined names used for cascading data validation lists
The data validation list formula for the Category column is simple:
=Categories. The data validation list formula for the Item column is a bit more complicated. It has to check the value of the corresponding Category entry and do one of three things: display no list if the Category entry has not been selected, display the list of fruits if Fruits has been selected, or display the list of vegetables if Vegetables has been selected. The formula that does this is shown here:
The Categories list is the data validation list for the Category column. The Fruits list is the data validation list for the Item column when the Category selected is Fruits. The Vegetables list is the data validation list for the Item column when the Category selected is Vegetables. Each of these lists has been given the worksheet-level defined name shown in the caption above their bor-der. Figure 4-23 shows all of the defined names used in this example.
=IF(ISBLANK(C3),””,INDIRECT(C3))
If the cell in the Category column is blank, the formula returns an empty string, which removes the data validation list from the Item cell next to it.
If the cell in the Category column has an entry, the formula uses the INDI-RECT worksheet function to coerce that Category column entry into a range reference. The range reference refers to either the Fruits list or the Vegetables list depending on which item the user selected in the Category column. As Figure 4-24 shows, this formula successfully displays two com-pletely different data validation lists depending on the category selection.
This logic can be extended to as many categories as you need. However, for cases with large numbers of categories, a table-driven approach that you’ll see used in our sample time sheet application is much easier to set up and maintain.
Note that one drawback of this type of validation is that it doesn’t work in both directions. In the scenario described previously, there is nothing to stop a user from accidentally changing the category entry in a row where a specific item has already been selected. In the next section you see how to use conditional formatting to provide a visual indication that this kind of error has been made.