Microsoft Office Tutorials and References
In Depth Information
Adding an Input Message
What this formula does is restrict the data entry in B5:B20 to a value no greater than the number
you’ve posted in A6, in this case 50. Change that highest-possible-grade value in A6, and your data
entry limit for B5:B20 changes accordingly. The formula requires the dollar sign before the 6 in A6,
because even though the formula is initially written in cell B5, because you’ve selected the range
B5:B20, Data Validation copies this formula to the other cells in the range; and because of relative
cell referencing, the A6 would otherwise revert to A7, A8, A9, etc. down the range. But we need to
hold to A6 throughout the range—because that’s where the grade value is stored.
Adding an Input Message
Data Validation also allows you to fashion a prompt, called an Input Message , which appears over the
cell in which you’re about to enter data, informing the user what sort of data can be entered in the cell as
per your data validation rule. To demonstrate by returning to the two-character state name data
validation rule, we can:
1. Select any range, say H3:H40. Leave the range selected.
2. Click Data Validation Data Validation Allow Text Length Data and
select “equal to” on the drop-down menu.
3. Enter 2 in the Length field.
4. Click the Input Message tab.
5. In the Title field enter 2 Characters, Please (or any suitable title.)
6. In the Input Message field, type: You Must Enter a Two-Character State
Abbreviation.
7. Click OK.
Rest your mouse over any cell in the H3:H40 range. You should see (Figure 6–57):
Figure 6–58. An Input Message
Using the Error Alert Option
And there’s another option you can implement with Data Validation, which enables you to modify the
restrictions on data entry in cells—the Error Alert option. Error Alert does more than simply alert the
user about various validations; it also lets you notify the user about a restriction—and then allows the
restricted data to be entered anyway .
1.
Select cells H3:H40 again, and select Data Validation Data Validation Error
Alert.
 
Search JabSto ::




Custom Search