Data Validation‎ > ‎

Update Prior Choices

 
    
                                                    Sample Sheet:    DV-CorrectPriorChoices.xls
PROBLEM:
"I have drop downs all over my spreadsheet. I use named ranges for the drop down evaluation lists. If I go to my original list and edit one of the choices in the list, correcting spelling or changing the wording of that choice, none of the cells on my workbook update to the new value(s).  They all still show the old value.  Is there an easy way to get these edits to reflect in the cells where the drop downs had been used already?"

APPROACH:
Since drop downs put actual values in the cells, they would not update if the source DV list were changed.  Only formulas will update automatically when the referenced cells change their values.  So what we will do is watch for any cell that has a DV list in it, and when a choice is made from the DV list, it will instantly be replaced with a formula that has the same result.  The cell will appear to just display the choice the user made, but it's really go a formula in that cell.  This way, updates to the source range will immediately update in the 'used' cells.

CAVEAT:
This won't help if you change the ORDER of your source lists.  If a person selects option 2 in a drop down, and you go back and edit option 2 it will reflect that change.  But if you move option 2 to position 3 in the list, whatever you put back in position 2 is what will appear in the already used cells.  Keep that in mind.

EXAMPLE:

We've created a named range called "Animals" in column F.
We've applied a DV List setting to cell A1 to use that list as a drop down.




When the drop down is used in A1, you can see the typo in the list.



Normally, when the user selects the option, you can see the value in formula bar is flat, it shows the same value selected. It is a text string.


What this macro will do is instantly replace the flat string with a formula that shows the same result, listing the "position" of the choice from the original list.


When you go back to the list later and correct anything, that correction will flow to the used cells since they are referencing the list via "formula".

This macro goes in the SHEET module where you want it active.

CODE

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Author:    Jerry Beaucaire,  8/12/2011
'Summary:   Make choices from DV drop downs into formulas, so any changes
'           in the source lists will flow out to the already filled in cells
Dim strValidationList As String
Dim strVal As String
Dim lngNum As Long

On Error GoTo Nevermind
strValidationList = Mid(Target.Validation.Formula1, 2)
strVal = Target.Value
lngNum = Application.WorksheetFunction.Match(strVal, Range(strValidationList), 0)

If strVal <> "" And lngNum > 0 Then
    Application.EnableEvents = False
    Target.Formula = "=INDEX(" & strValidationList & ", " & lngNum & ")"
End If

Nevermind:
    Application.EnableEvents = True
   
End Sub




Comments