Creating Error Alerts In Excel Using Data Validation

Mon 24th October 2011

Visitors to the website of a campsite can book in advance usding an Excel sheet. When the campsite tariffs change from high season to low season, the owner wants visitors to the website to be aware of these cheaper rates by creating an alert that is triggered if their intended date of arrival falls within the parameters of low season. Here's how he did it.

I have recently been getting in some late-season camping with my partner before putting my tent away for the winter. The light, balmy evenings of summer have been replaced by cooler, moonlit ones where darkness comes early, and the temperature drops considerably through the night, but we have some decent equipment and we were comfortable enough.

One of the benefits of camping so late in the season is that the campsite tariffs return to low season rates, which are significantly cheaper; £5.00 per night at the site I was on. This site is open all year round and the site owner told me that a lot of campers turn out for bonfire night and so another trip might be on the cards. And it is this change from low season to high season that will provide an illustration of one of Excel's many useful functions, the error message.

Our scenario is a campsite that is open all year round. Potential campers can book online using an Excel sheet. As high season ended and the cheaper low season has begun, the campsite owner wants to inform potential visitors of these cheaper rates. Here's how he could achieve this through the use of an error message.

In column A3 type Date of Arrival. The next cell in the row, B3 is where the camper will enter the date of their intended arrival on the site. It is this cell that we wish to modify through Data Validation.

With B3 selected, click on Data and select Validation from the list. This will open up the Data Validation dialog box. The first thing to do is select the type of error alert you wish to put in place. Click on the Error Alert tab and from the Style drop down list select the required type of message, in this case Information (it is worth making note of the other options for possible future use; Stop and Warning). Selecting Information will bring up the familiar icon of a lower-case letter I in a speech bubble.

With the type of error set, you can type in the message you wish to appear when a certain value is entered into that cell. In my case I want to create an alert that tells the enquirer that the arrival date they entered is subject to the low season tariff. In the Title box I typed 'Low Season Tariff', and in the Error box I typed the following message:

"Please note that on the date you selected the site will be operating low season tariffs."

Now that we have our message set up, we need to enter the dates that will trigger the alert. It is important to note that validating data in this way will require dates outside of the parameters I set to trigger the alert. So in the Data Validation dialog box I click on the Allow arrow and then select Date from the list. In the Data list I select Between, and then I enter the start and end dates as the start and end of high season - i.e., 03/05/2012 to 30/09/2012. Any date outside of these parameters, such as November 5th this year, that is entered will trigger the information alert.

This example offers information only. It does not prevent further advancement and the user can go on to book his camping trip, aware that he will be paying the low season tariff. The other options in the Error Alert tab also have their uses.

If the campsite closed for the winter, say from 1st October 2011 to 3rd May 2012, then a different error message could be set up to inform the user that the site is closed. Just as before the Date of Arrival cell would be selected and the Data Validation dialog box opened. This time the under the Error Alert tab, the style Stop is chosen to bring up another familiar icon, the white x inside a red disc.

In the Title box type the alert "Site Closed!" And in the Error box type the following message:

"This site will be closed on the date you selected".

Select the Settings tab and again insert the dates that will not trigger the error message, in this case the dates the site will be open next year: 03/05/2012 to 30/09/2012. Any date prior to the opening date of the site will trigger the error alert and, unlike the information alert above, the user will not be able to continue. The options available will be Retry or Cancel.

Excel has a multitude of tricks up its sleeve that can create so much more than basic values in cells. It is well worth taking the time to familiarise yourself with as many as possible.

Author is a freelance copywriter. For more information on excel training courses uk, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-2021-creating-error-alerts-in-excel-using-data-validation.html