Excel 2010 Bible

Chapter 25: Using Data Validation

577

FIGURE 25.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 formula ensures that the user’s entry is a text string that begins with the letter A (upper-

case or lowercase).

=LEFT(A1)=”a”

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.

The following formula is a variation of this validation formula. It uses wildcard characters in the

second argument of the COUNTIF function. In this case, the formula ensures that the entry begins

with the letter A and contains exactly five characters:

=COUNTIF(A1,”A????”)=1

Accepting dates by the day of the week

The following data validation formula ensures that the cell entry is a date, and that the date is a

Monday:

=WEEKDAY(A1)=2

This formula assumes that the active cell in the selected range is cell A1. It uses the WEEKDAY func-

tion, which returns 1 for Sunday, 2 for Monday, and so on.