Microsoft Office Tutorials and References
In Depth Information
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
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 19-29 shows these validation criteria in effect, using a
custom error alert message. The user is attempting to enter 16 into cell B5.
Figure 19-29: Using data validation to prevent duplicate entries in a range.
Accepting text that begins with a specific character
The following data validation formula demonstrates how to check for a specific character. In this
case, the formula ensures that the user’s entry is a text string that begins with the letter A (either
upper- or lowercase).
This formula assumes that the active cell in the selected range is cell A1.