Part IV: Managing and Analyzing Data
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 refer-
ence, and it adjusts for each cell in the validation range. Figure 26.6 shows this validation
criterion in effect using a custom error alert message. The user is attempting to enter 17
into cell B5.
FIGURE 26.6
Using data validation to prevent duplicate entries in a range
