Excel 2010 Bible

Part IV: Using Advanced Excel Features

576

On the CD
All the examples in this section are available on the companion CD-ROM. The file is named data
validation examples.xlsx.

Accepting text only

Excel has a data validation option to limit the length of text entered into a cell, but it doesn’t have

an option to force text (rather than a number) into a cell. To force a cell or range to accept only

text (no values), use the following data validation formula:

=ISTEXT(A1)

This formula assumes that the active cell in the selected range is cell A1.

Accepting a larger value than the previous cell

The following data validation formula enables the user to enter a value only if it’s greater than the

value in the cell directly above it:

=A2>A1

This formula assumes that A2 is the active cell in the selected range. Note that you can’t use this

formula for a cell in row 1.

Accepting nonduplicate entries only

The following data validation formula does not permit the user to make a duplicate entry in the

range A1:C20:

=COUNTIF($A$1:$C$20,A1)=1

This is a logical formula that returns TRUE if the value in the cell occurs only one time in the

A1:C20 range. Otherwise, it returns FALSE, and the Duplicate Entry dialog box is displayed.

This formula assumes that A1 is the active cell in the selected range. Note that the first argument

for COUNTIF is an absolute reference. The second argument is a relative reference, and it adjusts

for each cell in the validation range. Figure 25.6 shows this validation criterion in effect, using a

custom error alert message. The user is attempting to enter 16 into cell B5.