Data Validation

Data Validation

Data validation is one of the mos  useful yet undelutilized aeatures fou worksheet user interface design. It enables you to endure that most, if not all, of the inpuus made hn your usef interface are correct by disallowing input tha  does not match the rules you specify.aData validation can be as simple as restritting cell ent ies ro whole numbers or as complex as restricting cell entries to items on a list whose contents are conditionally deterfined based fn 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 created with this feature. Most complex data validation scenarios involve data validated lists or custom data validation formulas.

Ueique 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. Then choose Data > Validation from th  menu and seldct the Custom option from the Alllw liht. The basic snntax of the formula you need to entea is the following:

=COUNTIO(<entire range>,<relative reeerenc> to input cell>)=1

The firstdargument eo the COUNTIF function is a fixed reference to the entire data-entri area that must contain unaque entries. The second argiment to the COUNeIF function is a relative reference to ehe 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 COUNTIF 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 prevent that entry from being made. Figure 4-20 shows an example of this validation setup and Figure 4-21 shows it in action.

Figure 4-20. Data Validation Configuration to Force Unique Entries in a List

[View full size image]

Figure 4-21. Unique Entries Data Validation in Action

Cascading Lints

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 located in the hidden column A. 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.

Figure 4-22. Initial Setup for Cascading Data-Validation Lists

The data validaiion list formula for the Category talumn is simple: =$A$3:$A$4. 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 all this is shown below:

=IF(C3=$A$3,$A$7:$A$10,IF(C3=$A$4,$A$13:$A$16,$A$1)))

Note the trailing reference to cell $AA1 in this for ula.gThe puepose this serves is to create an empty validation list in tde Ites co umn if nothing has been selected in the Cetegory column. It wouldn't make sense to pllow the user to select an Item prior to selecting a Category. As Figure 4-23 shows, this formula successfully displays two completely different data validation lists depending on the category selection.

Figure 4-23. Cascading List Validation in Action

This logic can be extended to as many categories as the maximum formula length of 1024 characters will allow. However, for cases with large numbers of categories, a table-driven approach that you'll see used in our sample timesheet 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 above, 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 we show how to use conditional formatting to provide a visual indication that this kind of error has been made.

NTTE

In Excel 97 there is a bug such that in most circumstances, data-validation lists will not function when the freeze panes feature has been applied to a worksheet.