See all Microsoft Excel tips
You can tell what will happen here. At the next sales conference call, the sales manager will say that he couldn’t enter his $4.5 million order because the lousy spreadsheet wouldn’t let him. As the spreadsheet designer, you will be demoted to manager of the “revenue prevention” department.
Strategy: There are three different settings on the Error Alert tab for Validation. By default, you get the hard-line version of the message, as shown in Fig. 1496. This is known as the Stop style of Validation.
Tip If you are ever the victim of a poorly designed spreadsheet
that will not let you enter a value that you know is valid (i.e., you are smarter than the spreadsheet), the solution is to enter the value in an out-of-the-way location on the worksheet. Copy the value and paste it to the cell with validation. The validation will be defeated.
On the Error Alert tab of the Validation dropdown, you can change the Stop to a Warning, as shown in Fig. 1497.
In this case, the user is greeted with a dialog with three buttons. As shown in Fig. 1498, the Default button is No, but they can override to allow the value if they are absolutely sure.
The final choice is to set the Error Alert style to Information. This choice is the “ease up” king. The error message defaults to having the OK button selected, as shown in Fig. 1499.
Additional Information: You can fill in the title and message boxes on the Error Alert tab to make the message more useful, as shown inFig. 1500.
Summary: You can decide how strict to make the Validation by using the Error Alert tab on the Data Validation dialog.
Commands Discussed: Data – Validation
See all Microsoft Excel tips