Excel 2010 Bible

Part IV: Using Advanced Excel Features

574

Using Formulas for Data Validation Rules

For simple data validation, the data validation feature is quite straightforward and easy to use. The

real power of this feature, though, becomes apparent when you use data validation formulas.

Note
The formula that you specify must be a logical formula that returns either TRUE or FALSE. If the formula eval-
uates to TRUE, the data is considered valid and remains in the cell. If the formula evaluates to FALSE, a mes-
sage box appears that displays the message that you specify on the Error Alert tab of the Data Validation dialog
box. Specify a formula in the Data Validation dialog box by selecting the Custom option from the Allow drop-
down list of the Settings tab. Enter the formula directly into the Formula control, or enter a reference to a cell
that contains a formula. The Formula control appears on the Setting tab of the Data Validation dialog box
when the Custom option is selected. n

I have several examples of formulas used for data validation in the upcoming section “Data

Validation Examples.”

Understanding Cell References

If the formula that you enter into the Data Validation dialog box contains a cell reference, that ref-

erence is considered a relative reference, based on the upper-left cell in the selected range.

The following example clarifies this concept. Suppose that you want to allow only an odd number

to be entered into the range B2:B10. None of the Excel data validation rules can limit entry to odd

numbers, so a formula is required.

Follow these steps:

1. Select the range (B2:B10 for this example) and ensure that cell B2 is the active cell.


2. Choose Data ➪ Data Tools ➪ Data Validation. The Data Validation dialog box appears.


3. Click the Settings tab and select Custom from the Allow drop-down list.


4. Enter the following formula in the Formula field, as shown in Figure 25.5:
   =ISODD(B2)
   This formula uses the ISODD function, which returns TRUE if its numeric argument is an
   odd number. Notice that the formula refers to the active cell, which is cell B2.


5. On the Error Alert tab, choose Stop for the Style and then type An odd number is
   required here as the Error Message.


6. Click OK to close the Data Validation dialog box.

Notice that the formula entered contains a reference to the upper-left cell in the selected range.

This data validation formula was applied to a range of cells, so you might expect that each cell