Microsoft Office Tutorials and References
In Depth Information
The following formula is a variation of this validation formula. It uses wildcard characters in the second argu-
ment of the COUNTIF function. In this case, the formula ensures that the entry begins with the letter A and con-
tains 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 also 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 function,
which returns 1 for Sunday, 2 for Monday, and so on.
Accepting only values that don't exceed a total
Figure 20-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 ex-
ceed the budget:
=SUM(\$B\$1:\$B\$6)<=\$E\$5
Figure 20-7: Using data validation to ensure that the sum of a range does not exceed a certain value.
Creating a dependent list
Search JabSto ::

Custom Search