• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto change of validated cell when source in data validation changed


New Member

I have a sheet here that I use data validation on all columns in the master list tab, the list is generated in material color tab. How do I make it auto update when the list change?

Your help is appreciated!




New Member
I have tried this code but didn't work.
>>> use code - tags <<<
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'           6/8/2016 correction to allow named range to exist anywhere in the workbookDim strValidationList As StringDim strVal As StringDim lngNum As Long
On Error GoTo NevermindstrValidationList = Mid(Target.Validation.Formula1, 2)strVal = Target.ValuelngNum = Application.WorksheetFunction.Match(strVal, ThisWorkbook.Names(strValidationList).RefersToRange, 0)
If strVal <> "" And lngNum > 0 Then    Application.EnableEvents = False    Target.Formula = "=INDEX(" & strValidationList & ", " & lngNum & ")"End If
Nevermind:    Application.EnableEvents = True   End Sub
Jerry Beaucaire's - Excel Assistant - Update Prior Choices (google.com)