Chapter 26: Using Data Validation
26
This worksheet uses three named ranges:
Vegetables: A2:A15
Fruits: B2:B9
Meats: C2:C5Cell F2 contains data validation that uses this formula:
=INDIRECT($E$2)Therefore, the drop-down list displayed in F2 depends on the value displayed in cell E2.
Using Data Validation without Restricting Entry
The most common use of data validation is to prevent a user from entering invalid data.
But data validation can also be used as a component of your spreadsheet’s user interface
without actually preventing the user’s entry. The two examples of showing an input mes-
sage and making suggestions would require quite a bit of VBA programming to accomplish,
but it can be accomplished easily with data validation.
Showing an input message
Data validation provides a way to display a message when the user selects a cell. Normally,
this message would tell the user what would be considered invalid data for that cell to pre-
vent them from getting the error message (when they actually enter invalid data). But you
can use this message for anything.
Figure 26.9 shows an input message reminding the user to complete a previous step. The
Allow drop-down list is set to Any Value, so the user is not prevented from entering any-
thing into this cell. It’s just a reminder set to a cell that the user will likely use early in the
process of completing this workbook.
FIGURE 26.9
Data validation can be used to show messages to the user.