Chapter 25: Using Data Validation
571
- Specify the conditions by using the displayed controls. Your selection in Step 4 deter-
mines what other controls you can access. - (Optional) Click the Input Message tab and specify which message to display when
a user selects the cell. You can use this optional step to tell the user what type of data is
expected. If this step is omitted, no message will appear when the user selects the cell. - (Optional) Click the Error Alert tab and specify which error message to display
when a user makes an invalid entry. The selection for Style determines what choices
users have when they make invalid entries. To prevent an invalid entry, choose Stop. If
this step is omitted, a standard message will appear if the user makes an invalid entry. - Click OK. The cell or range contains the validation criteria you specified.
Caution
Even with data validation in effect, a user can enter invalid data. If the Style setting on the Error Alert tab of
the Data Validation dialog box is set to anything except Stop, invalid data can be entered. Also, remember that
data validation does not apply to the calculated results of formulas. In other words, if the cell contains a for-
mula, applying conditional formatting to that cell will have no effect. n
Types of Validation Criteria You Can Apply
From the Settings tab of the Data Validation dialog box, you can specify a wide variety of data vali-
dation criteria. The following options are available from the Allow drop-down list. Keep in mind
that the other controls on the Settings tab vary, depending on your choice from the Allow drop-
down list.l (^) Any Value: Selecting this option removes any existing data validation. Note, however,
that the input message, if any, still displays if the check box is checked on the Input
Message tab.
l Whole Number: The user must enter a whole number. You specify a valid range of whole
numbers by using the Data drop-down list. For example, you can specify that the entry
must be a whole number greater than or equal to 100.
l (^) Decimal: The user must enter a number. You specify a valid range of numbers by refining
the criteria from choices in the Data drop-down list. For example, you can specify that the
entry must be greater than or equal to 0 and less than or equal to 1.
l List: The user must choose from a list of entries you provide. This option is very useful,
and I discuss it in detail later in this chapter. (See “Creating a drop-down list.”).
l Date: The user must enter a date. You specify a valid date range from choices in the Data
drop-down list. For example, you can specify that the entered data must be greater than
or equal to January 1, 2010, and less than or equal to December 31, 2010.
l (^) Time: The user must enter a time. You specify a valid time range from choices in the Data
drop-down list. For example, you can specify that the entered data must be later than
12:00 p.m.