Excel 2010 Bible

Chapter 25: Using Data Validation

575

would contain the same data validation formula. Because you entered a relative cell reference as the

argument for the ISODD function, Excel adjusts the formula for the other cells in the B2:B10

range. To demonstrate that the reference is relative, select cell B5 and examine its formula dis-

played in the Data Validation dialog box. You’ll see that the formula for this cell is

=ISODD(B5)

FIGURE 25.5

Entering a data validation formula.

Generally, when entering a data validation formula for a range of cells, you use a reference to the

active cell, which is normally the upper-left cell in the selected range. An exception is when you

need to refer to a specific cell. For example, suppose that you select range A1:B10, and you want

your data validation to allow only values that are greater than C1. You would use this formula:

=A1>$C$1

In this case, the reference to cell C1 is an absolute reference; it will not be adjusted for the cells in

the selected range — which is just what you want. The data validation formula for cell A2 looks

like this:

=A2>$C$1

The relative cell reference is adjusted, but the absolute cell reference is not.

Data Validation Formula Examples

The following sections contain a few data validation examples that use a formula entered directly

into the Formula control on the Settings tab of the Data Validation dialog box. These examples

help you understand how to create your own Data Validation formulas.