Problem: I set up a worksheet with data validation to ease the job of the sales managers. One of the managers is entering an order for a brand new product. The product is so new that it does not appear in the product list. Using default Excel list validation, the rep will be nagged and prevented from entering the order for the new product.
- By default, data validation is pretty strict.
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 of the Data Validation dialog. The default is the hard-line version of the message, shown above. This is known as the Stop style of Validation.
On the Error Alert tab of the Validation dropdown, you can change Stop to Warning. With a warning, the person using the spreadsheet is greeted with a dialog box with Yes, No, Cancel, and Help buttons. The default button is No, but people can override and allow the value if they are absolutely sure. You should type a message to indicate this.
- Warning is probably the best setting.
When a sales rep enters incorrect data, he will see the message below. Of course, because the message is longer than five words, he will press Enter without reading the message. Because the default button is No, he will then need to choose from the list.
- No is the default button.
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. You will certainly end up with a lot of invalid data if you use this setting.