Chapter 26: Using Data Validation
26
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 (uppercase 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????")=1Accepting dates by the day of the week
The following data validation formula assumes that the cell entry is a date, and it ensures
that the date is a Monday:
=WEEKDAY(A1)=2This formula assumes that the active cell in the selected range is cell A1. It uses the
WEEKDAY function, which returns 1 for Sunday, 2 for Monday, and so on. Note that the
WEEKDAY function accepts any nonnegative value as an argument (not just dates).
Accepting only values that don’t exceed a total
Figure 26.7 shows a simple budget worksheet, with the budget item amounts in the range
B1:B6. The planned budget is in cell E5, and the user is attempting to enter a value in cell
B4 that would cause the total (cell E6) to exceed the budget. The following data validation
formula ensures that the sum of the budget items does not exceed the budget:
=SUM($B$1:$B$6)<=$E$5Creating a dependent list
As we described previously, you can use data validation to create a drop-down list in a cell
(see “Creating a Drop-Down List” earlier in this chapter). This section explains how to use
a drop-down list to control the entries that appear in a second drop-down list. In other
words, the second drop-down list is dependent upon the value selected in the first drop-
down list.