This note describes several tips about using validation features to prevent you finishing tasks before you begin! We do this by using formulas in the validation settings. Read on to find out more.
The examples in this article can be found in the Dates & Times sheet of Validation.xls.
Theo Callahan, in his article “Block that spreadsheet Error" covers the basics of spreadsheet validation very well so we shall not cover them here. Our earlier article overviews the basics too.
Finishing before you begin!
Recently we were asked to work on a spreadsheet that computes the number of hours being worked on a casual basis each work day. There is a sheet for each worker. For each day worked, the date and his or her start and finish times are entered, and the spreadsheet does the rest. The start and finish times are entered in time format. The admin staff don’t like having to use 24 hour format so they use 12 hour formats (e.g., 9:30 am and 5:30 pm) to enter the time data. Entering the start times is not a problem because 9:30 is a morning time regardless of the system used. There are problems when they forget the pm for an afternoon or evening time as shown here in C8.
It should be pretty obvious what is going on but this problem can be prevented by using a validation setting on the Stop Time field that prevents times that are earlier than the Start Time being entered.
Making the validation setting into a formula
When a formula is entered in the Validation Settings it becomes the criteria for the current row. Subsequent rows behave as if the criterion was copied down the column. The settings for the first data row are shown. The criteria settings for the next row will be Time, greater than, =B5.
Let’s go back a step and talk about setting up the validation on this column.
The columns in our spreadsheet, with good reason, are called Start and Stop Time. The “Start time” label for the validation dialog is a coincidence and is a consequence of selecting Time as the data to be allowed. If we had chosen between instead, it would have also wanted an “End time”. The formula for the Start time was created by “pointing and clicking’ on the spreadsheet cell in the normal manner.
A useful little trick
An easy way to set up the validation is to select the whole column first and then assign the settings. This is useful when you don’t know how many rows you will ultimately want the validation to apply for. In this case we know that we don’t want it to apply for the first few rows in the column including the field headings. Selecting these few cells and using the [Clear All] button to remove the validation from just these cells.
Subsequently working on an earlier day!
If you want to ensure that an earlier date shouldn’t be entered then this can be done with validation as well.
From a validation point of view the difference between this and the previous example is that here we want the formula to refer to the same field (column) in an earlier row in contrast to a different column in the same row.
The formula can be entered in a very similar fashion with just one difference. In this case it doesn’t make sense to start by apply it to the whole column as suggested in the previous section. This left us with the question “What is the easiest way to select from the first cell to be validated (A6 in this case) to the end of the column?” One way is to use the key sequence [Shift] + [End] + [↓] twice. The first usage selects to the end of the data and the second to the end of the column.
In this case you would probably want to change the Error Alert from the default of Stop to Warning. This means the person doing the data entry is advised of the problem but can choose to override it.
This article has described two situations where formulas can be usefully used in validations settings. In our next article we talk about using the List settings in validation.