Microsoft Office Tutorials and References
In Depth Information
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 valida-
tion range. Figure 20-6 shows this validation criterion in effect, using a custom error alert message. The user is
attempting to enter a value into cell B5 that already exists in the A1:C20 range.
Figure 20-6: 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 for-
mula ensures that the user's entry is a text string that begins with the letter A (uppercase or lowercase).
This is a logical formula that returns TRUE if the first character in the cell is the letter A . Otherwise, it returns
FALSE. This formula assumes that the active cell in the selected range is cell A1.