Data validation is one of the best features in Excel. You can use it to create drop down lists, or limit what users can enter in a cell.
Unfortunately, data validation isn’t perfect, or foolproof. Users can get around the limits, by pasting data into the cell, or by using the Clear All command in a data validation cell.
Data Validation Input Errors
Someone sent me a question this week, asking how to trap input errors, despite these data validation failings:
- Hello! I was wondering, to you have any idea how to error trap a date input? I will enter dates on a specific column (complete dates such as 11/05/2010) and error trap if the input has the year of 2011 and not 2010. I know data validation does that, but only if the cell is manually inputted. If the input in the cell is pasted, it does not do that anymore.
Add the Data Validation
In this example, an order form has two cells for dates – an Order Date, and a Delivery Date. To ensure that a date for the current year is entered, you can use formulas in the data validation, to set a minimum and a maximum date.
Start Date formula: =DATE(YEAR(TODAY()),1,1)
End Date formula: =DATE(YEAR(TODAY()),12,31)
Check the Date
If you’re concerned that users might paste values into the cell, or clear the data validation, you can add a formula check, to ensure that a valid date was entered.
In the order form, a date check formula is entered in column G, which can be hidden.
The formula in cell G9 is:
=OR(C9=””,YEAR(C9)=YEAR(TODAY()))
The formula is copied down to cell G10, and the result is FALSE, because the date in C10 is not in the current year.
Block the Invoice Total
In the Invoice total cell, the formula result is “Invalid Date”, if either of the date check cells contains FALSE. If both dates are in the current year, the total sum is shown.
Here is the formula from the Invoice Total cell:
=IF(COUNTIF(G9:G10,FALSE),”Invalid Date”,SUM(E13:E17))
Other Solutions
Instead of a formula check, there are other ways to ensure that users enter valid data.
For example, you could use Excel VBA to check specific cells before printing, and cancel the printing if the entries aren’t valid.
Have you used other methods to ensure that users don’t ignore your data validation cells?
____________
Long back, I had programmed a variety of Invoice and Order templates with VBA, Data Validation and Formula checks to ensure exactly such valid entries.
Most of the checks would be triggered by formulas returning TRUE or FALSE in hidden cells or cells outside the defined print area.
Some of the critical cells would also be setup to display an error message, instead of just T/F.
And conditional formatting would take care of showing the message in colours that grab attention.
One final cell would contain a formula to return a ‘combined result’ (using IF, AND, OR functions) after checking whether all the ‘check cells’ for T/F.
Finally, the BeforePrint and BeforeSave events would be used to trap errors before executing the print or save commands.
I have an Excel file that generates PivotTable reports based upon a date range the user enters. The data itself is manually entered by the user and one primary field is the Date column. Consequently I have an entire subroutine dedicated to making sure there are valid dates start and end dates for the report range, there are valid dates in the Date column, there are no future dates, the data itself has dates within the report’s date range, and a couple of other things not directly related to dates.
The subroutine is ultimately triggered by the user clicking a button to generate a report. Here is the code I use to check for a valid Start Date
‘ Check for invalid Start Date
If VarType(rngStart) vbDate Then
str = “Please enter a valid Start Date.”
MsgBox str, vbCritical, APPNAME
End
End If
This seems to work well within this subroutine because the main routine is cancelled by the End statement after the user gets a message they need a valid Start Date.