Microsoft Office Tutorials and References
In Depth Information
Explaining Data Validation Errors with Error Alerts
Click in the Source field, and select the range containing the drop-down
menu items—in our case =$A$2:$A$5, or whatever range you’re using.
Note the dollar signs appear automatically when you select the range,
because the drop-down menu will likely appear in a whole range of
cells—and Excel doesn’t want the drop-down source range for each of
the cells to change as a result of relative cell addressing (see Chapter 4),
i.e., A2:A5, A3:A6, A4:A7. Also remember that if you name the source
range—to say Dept—all you need do is type =Dept in the Source field,
and that’s it.
Click OK.
The drop-down menu should be ready to go in the selected cells. Just
click the drop-down arrow in each cell.
If you change any of the data whose entries appear in the drop-down—say VP to
VicePresident—that new phrase will appear in all subsequent data entry using the
dropdown menu. But existing records displaying VP won’t be changed.
NOTE: Instead of entering the range which contains the drop-down items in Source, you can
also enter the actual drop-down items directly in the Source field instead, by typing HR,
Marketing, etc., each separated by a comma. With this technique there are no dollar-sign
Explaining Data Validation Errors with Error Alerts
Once you’ve subjected a range to a data validation rule you may want to provide the
user with some information about what the rule does. We’ve already seen that Excel
does that by default, by broadcasting the “The value you entered is not valid” message
we saw in Figure 2–42. But you can customize that message via the Data Validation
Error Alert feature, so that the user is told exactly what sort of data is and is not
permitted in the cell. Thus you can compose a prompt that declares: “You must enter
two characters in this cell”—and you can also provide an Error Alert that warns the user
that the cell entry is wrong, but the cell will accept it anyway if the user wants to go
ahead anyway.
There are three sorts of Error Alerts:
Stop—Excel’s default, which blocks entries that violate the data
validation rule
Warning—This notifies the user that the data entry violates the rule,
but allows her either to try again or to override the rule
Information—This one sounds like the others, but it just tells the user
the data entry violates the rule—and goes ahead and accepts it
Search JabSto ::

Custom Search