I answered a question over at superuser.com the other day having to do with syncing data validation. If you use data validation that links to a range, and you change the range, the data may no longer be valid. I have a range in A1:A3 that I’m using as a data validation list. In D7, I’ve selected the first item.
Now if I go change A1, the D7 value is no longer valid. Unless, I have some VBA to keep it in sync.
Here’s the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim rFound As Range
‘Only run this when a cell in Foobar is changed
If Not Intersect(Target, Me.Range(“Foobar”)) Is Nothing Then
‘Go through every data validation cell in the sheet
For Each rCell In Me.Cells.SpecialCells(xlCellTypeAllValidation).Cells
‘if the DV in the cell points to foobar
If rCell.Validation.Formula1 = “=Foobar” Then
‘See if the cell’s value is in the Foobar list
Set rFound = Me.Range(“Foobar”).Find(rCell.Value, , xlValues, xlWhole)
‘If it’s not in the list, it must be the one that
‘changed, so changed it
If rFound Is Nothing Then
Application.EnableEvents = False
rCell.Value = Target.Value
Application.EnableEvents = True
End If
End If
Next rCell
End If
End Sub
Dim rCell As Range
Dim rFound As Range
‘Only run this when a cell in Foobar is changed
If Not Intersect(Target, Me.Range(“Foobar”)) Is Nothing Then
‘Go through every data validation cell in the sheet
For Each rCell In Me.Cells.SpecialCells(xlCellTypeAllValidation).Cells
‘if the DV in the cell points to foobar
If rCell.Validation.Formula1 = “=Foobar” Then
‘See if the cell’s value is in the Foobar list
Set rFound = Me.Range(“Foobar”).Find(rCell.Value, , xlValues, xlWhole)
‘If it’s not in the list, it must be the one that
‘changed, so changed it
If rFound Is Nothing Then
Application.EnableEvents = False
rCell.Value = Target.Value
Application.EnableEvents = True
End If
End If
Next rCell
End If
End Sub
I think the next step is to make it more generic so it will work with any named range. I’ve never personally needed it, though.
What you identified is one major limitation of ‘Data Validation.’ The other is that one can paste a value into a cell with d.v. and the d.v. will disappear!
A solution that works around both those problems is to use another cell (locked) that validates the data. So, in your case, in E7 enter a formula that verifies that D7 is in A1:A3. If you have a model that uses D7 for downstream analysis, use E7 to ‘invalidate’ the downstream result.
So, suppose the ‘downstream model’ consisted of D9 containing VLOOKUP(D7,L1:M10,2,FALSE).
Now, in E7 enter the formula =IF(ISERROR(MATCH(D7,A1:A3,0)),”Error!”,””) and modify D9 to be =IF(E7=”Error!”,-1,VLOOKUP(D7,L1:M10,2,FALSE))
For a couple of uses see
Validate Mandatory Data
http://www.tushar-mehta.com/excel/newsgroups/validate_data/index.html
Hmmm. Not sure I need code. Data Validation using A1:A3 source range is limited to the same worksheet (as far as I know). So I routinely use rangenames for Data Validation so that I can put the source range in another worksheet.
Next, to add/delete cells (rows) in the source range, you should be able to use a formula instead of a static address block. Or even more simply for Excel 2007 and later, you create an Excel 2007 table and then superimpose an Excel rangename over the single column table.
Am I missing something?
I love the flexability of VBA and use it alot. However, IF it is possible to use Excel’s built-in functionality to achieve an objective then that’s what I’d opt for. In this instance I’d probably use conditional formatting to make it obvious that our data validation is ‘out if sync’ with source. It doesn’t do the nifty automatic updating that the VBA option provides, but I’d probably prefer this anyhow…
Dan
Dick: I like how you started the reply over at superuser with a “This seems dangerous, but…” :-)
I like this idea and have given some thought to it myself, but haven’t quite figured out how to apply it. In the case of one of my applications, I have a dynamic range of dates, which are used to determine when to mark down merchandise. Problem is, these dates are all fluid. They can change at any time during the buy.
So, what I haven’t figured out (without adding yet another table as a control) is how to keep validation in sync when multiple entries in the range could change.
I like your solution, but it looks to me like it’s only good if one value changes. I suppose I could have the users run the process each time they make a change, so that if they have three dates to update in one sitting, they update one, run the routine, update the second, run the routine, update the third, run the routine. But this seems like a lot to ask them to do…
Another important design consideration here is whether you want (or need) to store the history of the User’s selections. Sure, the data validation list may have changed, but perhaps you’d rather store the User’s selection at the time he made it, rather than force a reselection. Because of this and the other reasons mentioned above, I would advise caution when using this method.
Tushar: IIRC, the DV paste “bug” was resolved in Excel 2010.
Jan,
in 2010, if you do a normal paste on a validated cell, it will still clear the validation
Looks like my memory failed me. Alas.
[…] Daily Dose of Excel » Blog Archive » Syncing Data Validation […]
i could not extend dropdown box greater than 25. how can i insert more than 25 drop down box in my excel chart? i already make excel chart i don’t want delete i want continue that’s file. is it possible?