 |
|
|
The Validation feature in Excel was introduced in Excel 97 and has proved to be a very valuable tool. It's purpose is to allow the designer of a spreadsheet to restrict the entries that a user inputs into certain cells. Before this feature was available this was only possible through the use of Visual Basic for Applications (VBA). The feature itself is quite flexible and even allows the designer to type in their own unique message informing the user what type of entry is expected and/or why their entry is not allowed.
One thing you can be certain of when allowing other users to input into a spreadsheet you have designed is that they undoubtedly will input incorrect data at some time or another. This can be disastrous if the cell that has the incorrect data is one that feeds many other cells, which in turn feed others and so on... You can end up with a snowball effect through from one Worksheet to many Workbooks. Trying to find origin of the problem can be a nightmare in itself.
I would highly recommend this feature to anybody that writes spreadsheets, even if they are the only user of it! I mean let's face it, nobody's perfect and if something can go wrong it probably will!.
**One very important factor you should be aware of with Data Validation is that it does not prevent a person from pasting in invalid data or from deleting data. Not only this, but if they paste in data from another cell, the Data Validation will be removed. If the cell they copy from also contains Data Validation it will replace the Data Validation in the destination cell. The latter two problems will not happen if they use any of the Paste Special operations with the exception of All and Validation. The data content of the copied cell will still be accepted though.**
The Validation feature in Excel can be found under Data on the Worksheet Menu Bar. When first activated you will see the Data Validation dialog box. This consists of three page tabs Settings, Input Message and Error Alert. Let's look at these in detail.
This is the main page of the Validation dialog and will be used each and every time Validation is applied. By default, the Validation will apply to the selected cell(s) at the time of activating the Validation feature. There are two boxes with drop down arrows on this page, these are called ComboBoxes as they offer a combination of choices. The first ComboBox is the Allow: box. This is where you set the type of Validation criteria you wish to allow. By default it will always be on Any Value if the selected cell(s) contain no Validation.
The second ComboBox is the Data box. This will be disabled by default also. The purpose of this box is to allow you to select more limits on the data type you select from the Allow: box. These will be such limits as Equal to, Less than, Greater than etc. This box will become enabled depending on the data type you select from the Allow: box.
Let's go through these choices from the Allow: box:
Whole number/Decimal
By selecting either of these choices you are ensuring that either a whole number only can be entered or, if you select Decimal, only a decimal number can be entered. You will have noticed that when we select either of these options that two more input boxes appear and the Data box becomes enabled and should read Between as a default.
These two boxes are where you set the limits for the data type chosen. Depending on your choice from the Data box these will be Minimum: and Maximum:, Value:, Minimum: or Maximum:. If we have Whole number and Between chosen we could type the number 1 in the Minimum box and 10 in the Maximum box. This would mean we have restricted the entries for our selected cell(s) to accepting values between and including 1 and 10. The same would apply to decimals had we chosen Decimal and used two decimals for our Minimum and Maximum.
No matter what you choose from the Data box you can, if you wish, use cell references for your limits. The cell(s) that are referenced can contain raw data or can get their data as a result of a formula. So using the above example again we could set our Minimum to =A1 and our Maximum to =A2 Both A1 and A2 could have formulas that change the value of the cells depending on another criteria. For example if at the time of setting the Minimum and Maximum cells A1 and A2 had the values of 1 and 10 respectively, the Validated cell(s) would be restricted to these values. If at any time the value in either cell changed the Minimum and Maximum would be the same as their respective cells.
List
This particular choice can make life a lot easier for the user as well as the spreadsheet owner. It enables us to have a drop down list appear in the selected cell(s) that a user can select from. When a choice is made, the only input box available will be Source. It is this box that will contain our List or a reference to our List. Should you only have a very short list eg; Yes, No or True, False etc you can simply type the list directly into the List box exactly as shown. In other words each entry must be separated by a comma.
We should note here that if the In-cell dropdown checkbox is not checked there will be no dropdown list when the Validated cell(s) are chosen. However, they will still be restricted to the entries in the List chosen. To see what we mean try this.
Type January in cell A1 and drag this down with the Fill handle (small black square, bottom right of selection) to A8. You should have January to August in cells A1 to A8.
Select cell C1 and go to Data>Validation. Select List from the Allow: box .
Click the collapse dialog box (right of Source: box) and select A1:A12. Click the collapse dialog box again.
Ensure the Ignore blank and In-cell dropdown checkboxes are both checked and click OK.
Now to the right of cell C1, you will see a drop down arrow. Click on the arrow and you will have a list of the calendar months. Selecting any of the entries will place it in the cell. Now type: September in the cell A9 and go back to cell C1. The month September should now be part of your list. Entries will continue to be added to our list until we reach cell A12. Unfortunately this cannot be done with named ranges unless you use a Dynamic Named Range. Which ever way you go you should hide the column containing your list and/or protect the sheet.
One problem that you may encounter when using this method is that the Data Validation will not allow you to refer to a List on another Worksheet. This is where a bit of trickery is called for. Let's say the List we have just set up is on Sheet1, in say cell A1. On another sheet type the text: Sheet1!A1:A12 then select the cell(s) that you want to contain the list and again select Select List from the Allow: box. In the Source type: =INDIRECT($A$1) and then click OK. The same problem does not arise if you use a named range, but then any blank cells within the range will be displayed. So there are pros and cons to both methods.
Date, Time
When either of these two options is chosen from the Allow: box you will have the same choices as when you chose Whole number or Decimal. The only difference will be the titles for the Limit boxes. These will read Start date: and End date:, Date:, Start date: , End date: in the case of Date. or Start time: and End time:, Time:, Start time: , End time: in the case of Time. The dates or times can be typed directly into these Limit boxes or used as a reference to cells containing dates or times. As with the Whole number and Decimal the cell(s) can contain formulas.
If you want to enter a Date and Time (such as between: 22/5/2005 05:00 and 23/5/2005 05:00). You must use the Date choice from the Allow: box. All Dates and Times must be entered in a recognised format.
Text length
With options you can restrict the entry to a nominated amount of characters. You will be presented with the same choices from the Limit boxes as you are when you use Whole number or Decimal. You again can type directly in the Limit boxes or reference them to other cells. Whichever method you choose though, Excel will always use the numeric value of the cell(s) and not the text itself. By this we mean if we used Equal to from the Data box and then referred the Length Limit box to cell A1, which contained the text: May we have NOT set the Text length to 3 but rather to 77. This is because the text May has a Code value of 77. This can be seen by using the formula: =CODE("May"). To set the Text length to 3 (or whatever cell A1 is displaying) you would use: =LEN(A1) in the Length Limit box. I will discuss the use of formulas soon!
BTW: Len returns the number of characters in a text string.
You should also note that numeric entries will be treated the same way as text. By this we mean if you set the Text length to 3 you would be able to type a 3 digit number regardless of it's value.
Custom
If none of the above are suitable for what you want, then you would use Custom. By using this you can set the criteria to just about anything you want. You do this by using a formula in the Formula: Limit box. The formula can be a simple one or a very complicated nested one. The formula that we use in the Formula: Limit Box should evaluate to either TRUE or FALSE.
So for example we could Validate cell B1 with a formula like: =SUM($A$1:$A$20)>500 This would mean that unless the SUM of cells A1:A20 was greater than 500 we could not enter a value in cell B1.
Or another example would be if we again Validated cell B1, but this time used the formula: =$A$21=SUM($A$1:$A$20) This would mean we could not enter a value into cell B1 unless cell A21 was equal to the SUM of the cells A1:A20
As you can see in both cases the formula used would evaluate to either TRUE or FALSE.
Perhaps one of the most useful functions for this choice is to prevent users from typing an entry in a List or Table that already exists. In other words to prevent the duplication of data.
Assume you have set up a table for input within the range A2:D500 we could use the Custom choice to ensure that no two entries are the same within the whole table. This is how:
Select the range A2:D500 starting from cell A2. This will mean that although we have selected cells A2:D500 only cell A2 will be our Active cell. You can only ever have one cell Active at a time!
Select Custom from the Allow: box. Type: =COUNTIF($A$2:$D$500,A2)=1 in the Formula box.
It is important to use the absolution of $A$2:$D$500 and a relative reference for A2. This way Excel will apply the correct formula to all the other cells in the range A2:D500.
Click OK
Now you will only be able to type an entry in the range A2:D500 if it does not yet appear! If you wanted to restrict the entries to be unique to the same column, you could do it this way:
Select the range A2:D500 starting from cell A2.
Select Custom from the Allow: box. Type: =COUNTIF(A$2:D$500,A2)=1 in the Formula box.
It is important to use the absolution of the Rows but NOT the Columns and a relative reference for A2.
Click OK
This will mean the user cannot type an entry in say cell A20, if it already exists in the A2:A500, but could if it was in any other cell and not in A2:A500.
Although we can use the Custom option to enter a formula we can also use one of the Limit boxes to hold a formula, as we did with Text length ie; =LEN(A1). If we do use a formula in any Limit box when we have chosen an option other than Custom from the Data box the formula must evaluate to a Numeric result as opposed to a logical result such as TRUE or FALSE.
There are two more options on this page that we have not looked at yet. These are Ignore blank and Apply these changes to all other cells with the same settings.
Ignore blank
When this option is checked it means that any cells that are being referred to which are currently blank will be ignored. For example let's assume we have Validated cell A1 to only accept a Whole number between 1 and 10, the Minimum and Maximum are being fed from cells B1 and B2 respectively. If we had no values in these two cells as yet and we had the Ignore blank checkbox checked we could enter any value we like in A1 until such time as there are values in both B1 and B2. If we unchecked this box and B1 and B2 are blank the Validation would assume A1 cannot except any values at all. This is because we have told Validation that the Minimum and Maximum are 0 and 0 (zero and zero). Remember a blank cell has a value of 0 (zero).
This would not be the case if cells B1 and B2 contained formulas that were returning " " (empty text) this would stop all entries if the Ignore blank checkbox was unchecked. This is because our cells B1 and B2 are not blank.
Apply these changes to all other cells with the same settings
This checkbox is to make life easier if you decide to change the settings for a Validated cell when you have many others that contain the same Validation settings. If you can imagine you have 200 cells distributed throughout a Worksheet and they all have the same Validation settings. Now let's say the upper (Maximum) Limit needs to be changed from 10 to 15. All you need to do is select one of the 200 cells and make the change and then check the Apply these changes to all other cells with the same settings box and click OK. All the other 199 cells will also be changed. This has got to be easier than trying to locate all 200 cells. Although while we are on the subject you could select one of the 200 cells then push F5, click Special then check the Data validation option button and the Same Option button then click OK. This would automatically select all cells that have the same Data Validation.
Let's now look at the other two page tabs of the Data Validation dialog box.
It is on this page that you can type a message for the user to see whenever a cell that is Validated is selected. The purpose of this would normally be used to inform the user of the Data type that will be accepted or is expected.
Show input message when cell is selected.
If checked this checkbox will simply show the message (if any) when the cell is selected. If you check this box without supplying a Title and/or a Input message no message will be displayed.
Title:
This is where you should type a brief title for your message.
Input message:
As mentioned, this message will be displayed whenever a cell that is Validated is selected. We advise you to make this as brief and to the point as possible and include the parameters if applicable eg: Must be a whole number between 1 and 10. Remember this message will be displayed each and every time a user selects the cell(s).
This page is very similar to the Input Message page.
Show error alert after invalid data is entered
When checked this will allow the message you have typed (if any) to be displayed whenever invalid data is entered. The user must attempt to leave the cell before seeing the message.
Title:
This is where you should type a brief title for your message.
Error message:
The message you type should ideally explain in a brief, to the point manner why their data is not being accepted. eg: The data you have entered falls outside the allowed range of a whole number between 1 and 10!
Style:
This is where you will set the level of chosen Validation. There are three available:
Stop: Your message will be displayed as an Error and the data will not be accepted at all! They will have the option to either Retry or Cancel. This setting is the default if no Style is chosen.
Warning: Your message will be displayed as a Warning and the user will have three options: Yes meaning the data will be accepted. No meaning they data will not be accepted and they will need to enter another value. Cancel meaning their data will be removed and the previous entry will be displayed. The default button is No.
Information: Your message is displayed as Information. The user will have two options, OK the data will be accepted. Cancel the data will be removed and the previous entry displayed. The default button is OK.
There is one other button on the Data Validation dialog (not including OK and Cancel) and that is the Clear All button. This button will simply clear the Validation for the selected cell(s) only. It will not effect any other cells!
So as you can see Data Validation can make both the user and the spreadsheet writers life a lot easier when used in the right manner. Just remember that it will not prevent a user from pasting in invalid data, regardless of the Error Style chosen, they will not receive any Error message. Any user can also delete the contents of a Validated cell.
There is another tool that can be used in conjunction with Data Validation and it can be found on the Auditing toolbar. Go to View>Toolbars>Formula Auditing or Tools>Formula Auditing>Show Formula Auditing Toolbar. On the far right of this toolbar there is the Circle invalid data and Clear validation circles icons. If you push the Circle invalid data button all cells that have Data Validation and contain data that falls outside the Limits set will be circled with a red circle. This means all cells that have the Error Style set to Warning or Information. Pushing the Clear validation circles will remove all circles.