MrExcel.com - Your One Stop for Excel Tips & Solutions

Configure Validation to “Ease up”

January 22, 2024 - by

Configure Validation to “Ease up”

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.

If you type something not in the list, then you get a message The Value You Entered Is Not Valid. A User Has Restricted values that can be entered into this cell. Buttons are Retry, Cancel, and Help.
Figure 1481. 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.

The Error Alert tab of Data Validation allows you to customize the message with a Title, Error Message, and Style. By changing the Style from Error to Warning, the person using your worksheet will be able to override the validation, but they will have to click Yes when Yes is not the default button.
Figure 1482. 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.

A customized Data Validation error box. Title says Item Not In The List. The message: If this is a brand new product, click Yes. Otherwise please choose No and select from the list Continue? The buttons are Yes, No, Cancel, and Help.
Figure 1483. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Angèle Kamp on Unsplash