Data Validation can be applied to restrict a cell’s allowed contents to a range of predefined values or rules. This is a subtle and effective way of allowing the model developer to control the way in which a model user can input data.
This is element of control is important because underlying calculations are often prepared to only allow specific inputs, such as integers, dates, percentages etc.
Data Validation can be found under the menu Data > Data Validation and is really useful for:
- Restriction of a cell’s content to a predefined list such as Yes,No or On, Off, Scenario 1, Base Case, etc
- Notifying the user on how to use the cell
- Showing a customisable Error Alert if a value is incorrectly entered
1. Restricting a cell’s input
A good model shouldn’t require over-engineered navigation or inputs constraints to control the user but experience suggests that some parameters such as dates and tenors are best not allowed too much optionality.
For example if the model and debt repayment is on a quarterly basis then a tenor input 12.66 years would be misleading (although may still work), the user should 12.50 or 12.75. These options would be provided by having a range named list of ….11.75,12.00,12.25,12.50,12.75,13.0…. etc This approach is also useful when providing multiple profiles of a time based assumption such as a price or growth rate.
Applying Data Validation based on a List creates a drop down list from which the user can select. Setting this up is quite straight forward.
Dynamically linking the Source to other cells means that any changes in the cell values, such as the name of a price path, will be automatically updated in the contents of the drop down list. This change however will only be updated the next time the drop down box is selected which can cause some temporary undesired consequences so be sure to watch out for that. If your list is off the worksheet you will need to use a range name approach which is generally advised anyway for sense checking and general ‘auditability’.
This same approach is also effective for date assumptions and is important because model mechanics often inherently assume that events happen at the beginning or the end of a period, and so restricting a date assumption to either a start or end date will ensure compatibility with the model’s underlying calculations.
2. Notification on how to use a cell
These Input Messages can be programmed to appear when a particular cell is selected, notifying the user on how that cell is intended to be populated. This is an informative way to control a cell’s contents without using any excessive direct cell modifications.
In this example, it can be seen how the Input Message will help let the user know that the timing data entered should an end of month date. This does not stop the user entering an alternative value being entered so is a softer way controlling input.
3. Error Alert
An Error Alert can be set to appear when data is entered into an input cell in a way that breaches the way the model builder intended. When Data Validation controls are applied, a standard error message appears when invalid data is entered. This message can be customised to give the user a clearer picture of the incompatibility.
In the example below, the number of Annual Repayments input is designed to contain whole numbers only, and an invalid entry triggers the warning message communicating to the user that only whole numbers are to entered into this cell.
There are some subtleties to the use of Data Validation and it is worth exploring some of the options available next time you build a model. Remember though like many features of Excel, excessive use of any one feature can cause frustration of over-engineering.
Hi Nick,
Thanks for a good overview of this really useful tool.
I particularly like your comment at the end about excessive use. When users discover this feature they often get Data Validation Fever and start using it everywhere!
I wondered if anyone has been able to come up with a solution for the problem of users being able to paste invalid data straight into the cell? It’s frustrating when you have gone to all the trouble of creating a data validation to restrict what users can enter into a cell, and then it is quite easily for a user to copy and paste into the restricted cell, so bypassing the validation.
Regards,
Danielle Stein Fairhurst
Plum Solutions.
Hi Danielle,
I personally have not got a solution for completely restricting a users input into a cell, maybe Excel needs a Data Restriction functionality as well as Data Validation?! I would be surprised if this can’t be performed with VBA and/or cell/sheet protection properties, but I am not sure the problem is worth the introduction of a script or a potentially convoluted protection set-up just for this.
I guess this is another example of the difference between a financial model and ‘software’ and highlights the need for models to incorporate some element of control but really reinforce messages to users through appropriate labelling and formatting when a value is allowed and when its not.
Having a user paste over a cell in this way isn’t common experience for me (Navigator) although earlier this year we (Navigator) were engaged by a European avionics and aerospace engineering firm to construct a template model for their project teams for contract bidding. The model was to be used company wide and was lets say ‘comprehensive’, we located all data validated inputs in a ‘set-up’ sheet which was aside from the usual model inputs. So a user could still paste over a cell but it was visually and location wise, so obviously “only” a choice that the chances of this happening were at least minimised.
I would also be interested if there is a simple way to accomplish Data Restriction….
Nick
Hello Danielle
You will need to employ VBA in your model to prohibit copy and paste. A general a simple method will be to exit CutCopy mode each time a selection changes in a sheet. The following will need to be pasted into the relevant sheet module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CutCopyMode = False
End Sub
To prohibit copy and paste over a validation cell:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo Finish
If Target.Validation.Type Then
Application.CutCopyMode = False
End If
Finish:
End Sub
Or see this article if you want to disable all copy and paste controls and shortcuts:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=373
Further, I use the sheet change event regularly in my models. It is a common problem for me and I much prefer this method. I have seen some modellers go to extremes by using a complete VBA approach as a substitute to validation. But in my view native validation is so powerful and very easy to set-up (for a developer). Restricting paste over validation is achieved with 7 lines of code attached to the sheet module. You can also attach to the ThisWorkbook module if you want it true on all sheets. You would then use the Workbook_SheetChange event:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo Finish
If Target.Validation.Type Then
Application.CutCopyMode = False
End If
Finish:
End Sub
I’m not either a big fan of disabling the controls. I do disable controls and build custom controls in my models but then I am very cautious of how I do it. If you don’t get it right then you run the risk of having the controls disabled when the user switches to other workbooks too.
Hi Rickard
I thought it may be useful; to validate that a cell only houses a date that is equal to the last daye of the month, use the following formula in validation type custom:
=A1=DATE(YEAR(A1),MONTH(A1)+1,0)
Regards
Jon
Hi all,
I tried Jon’s method and experienced a few glitches, maybe you could check them out for me. The problem I encountered was that
1.The way that the code works means that if I were to copy a cell (Ctrl – C and get the dotted line box around the cell), and accidentally moved my cursor into a data validation cell, I can no longer “paste”, which means that every time I select a cell with data validation, Excel seemed to lose its memory of the cell that I was suppose to copy/paste.
2. The method means that every time your selection changes, the macro will be run (could potentially lead to speed problems and seems over-engineered unless this functionality was mission critical).
Suggestions:
1. Protect the cells the usual way with data validation.
Tools -> Protection -> Allow Users to Edit Ranges -> Select the cells with data validations
2. Format the data validation cell to be protected, using
Tools -> Protection -> Protect Sheet -> Password Optional -> OK
The data validation ability of the cell will be retained even after copy paste.
Or as a suggestion in VBA, in the code module for the worksheet that you have your data validation in, use the code below, this will undo the action if you have pasted on a data validation cell. I am sorry I have not annotated it very well !
Private Sub Worksheet_Change(ByVal Target As Range)
‘Does the validation range still have validation?
If HasValidation(Range(Target.Address)) Then
Exit Sub
Else
Application.Undo
MsgBox “Your last operation was canceled.” & _
“It would have deleted data validation rules.”, vbCritical
End If
End Sub
Private Function HasValidation(r) As Boolean
‘ Returns True if every cell in Range r uses Data Validation
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function
Nick,
Couldn’t get a stable result from the VBA code…seemed to enter an eternal loop when copying and pasting to a non-validation cell. Do you encounter same?
My first thought was along Jon’s lines of setting the Application.CutCopyMode to false to scrub the copying – but I take your point about accidental selection.
The other option would be to use a Combobox on the sheet, and do away with the data validation cell altogether
Chris.
@ Chris
Using a combo / listbox assumes that you are only attempting to prevent a user from overwriting a list validation. And configuring a combo / listbox inevitably means that you must write the selection to a cell anyway so it is prone to overwrite too.
@ Nick
Speed certainly isn’t an isse with regards to my first suggestion. A simple change event that cancels CutCopy is incredibly quick and it doesn’t trigger a recalc or anything. Point taken about it being annoying – but did you read the article from VBAX that shows how to disable the controls? This could easily be tailored to rstrict paste over validation cells.
@ Jon
Shouldn’t be too prone to overwrite if you call the box contents (ListFillRange or Input Range) from a range name in a hidden sheet, and write the result (LinkedCell or Cell link) to a range name in a hidden sheet…should avoid unwanted interaction, be that copy/paste etc.
Also it’s a no-code solution…
Chris
I do tend to keep sheets dedicated to validation lists. But creating an additional dependency (i.e., write output to another hidden range) is an unnecessary dependency and makes tracking just that little more complex. I like a ‘keep it simple’ approach and therefore I would not use a combobox as a substitute to list validation unless I specifically wanted to employ a VBA procedure to trigger based on the user selection.
Although I avoid worksheet controls as much as possible, especiallly ActiveX controls which are prone to various bugs. Regardless of the type each control has a cololection of its’ own properties each of which needs consideration and adds that little extra unnecessary complexity. Instead if there is any genuine value offered by a control (list / combobox etc) I tend to use forms or toolbar controls.
In short, my view is to use Excels native in-built features and only ever consider more complex solutions such as controls / VBA if I think it will offer significant improvement. This is not true in this instance since I have already suggested different appoaches to avoiding validation overwrite.