Update Data from Drop Down List on Source Changes

[cperez]

Hi there, I need help with Data Validation in my workbook. I have two worksheets, the first is called Data and the second is called Dropdown. The Dropdown sheet contains three named ranges: ddTest1, ddTest2 and ddTest3. These named ranges are used in the Data worksheet to fill the data needed. My problem is that if I change the names in any of the named ranges, the existing values in the Data worksheet do not get updated with the new info.
I can see the new names in the dropdown lists for future use.
How can I get the information in the Data worksheet to automatically update if there are any changes in the Dropdown worksheet?
Thank you

 

[AlanSidman]

You have two choices, you can write some VBA code that will search for A and when found change it to B or you can use the built in Find and Replace function found on the ribbon. It is located on the Home tab -->Editing-->Find and Select

 

[cperez]

You have two choices, you can write some VBA code that will search for A and when found change it to B or you can use the built in Find and Replace function found on the ribbon. It is located on the Home tab -->Editing-->Find and Select

Thank you AlanSidman. Your suggestions will definitely work. I was hoping to update the data automatically when the source changes are made.

 

[Deepak]

cperez

Seems this possible by VBA but you have asked in excel section. Do i move the thread there?

 

[cperez]

cperez

Seems this possible by VBA but you have asked in excel section. Do i move the thread there?

Yes please. Sorry about that, I'm new to the forum.

 

[AlanSidman]

Option Explicit

Sub ChangeDD()
    Dim c As Range
    Dim rng As Range
    Dim lr As Long, lc As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    lc = Cells(3, Columns.Count).End(xlToLeft).Column
    Set rng = Range(Cells(3, 3), Cells(lr, lc))
    For Each c In rng
        If c = "Alpha" Then c = "Alpha Test"
        If c = "Blue" Then c = "Blue Test"
        If c = "Triangle" Then c = "Triangle Test"
    Next c
    MsgBox "completed action"
End Sub

 

[cperez]

Thank you AlanSidman. Your code works perfectly. Is there a way to improve on it to include InputBoxes for the columns in the range to be changed and for the data to be changed and the new data?