• 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

    Hui...

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

Update Data from Drop Down List on Source Changes

C

cperez

New Member
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
 

Attachments

  • Dropdown Source Changes.xlsx
    11.1 KB · Views: 9
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
 
AlanSidman said:
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
Click to expand...
Thank you AlanSidman. Your suggestions will definitely work. I was hoping to update the data automatically when the source changes are made.
 
Code: 
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
 
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?
 
You must log in or register to reply here.
Back
Top