99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Creating Error Alerts In Excel Using Data Validation
Mon 24th October 2011
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.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
PowerPoint Intermediate Advanced
Excellent course, Max was brilliant. I feel ready to do PPT whenever the team need me to do anything for them.
Also love the short cuts we learnt along the way! (windows D!)
Very good, helpful and flexible. I will get more out of our computer software after this experience.
AXA Investment Managers
PowerPoint Intermediate Advanced
It was an excellent course and tailored to our individual knowledge levels.