• 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.

Automatically change validated entries in sheets when source of validation list changes

KingCST

New Member
Hi,

First of all i want to point the fact that i did search on the forum before asking this question and i could not find a clear solution.

So, here's my issue in case someone is kind enough and has time to help me.

I've created a sample from my excel file with multiple sheets:

- one sheet (Source) contains a list with the names of the workers
- the three other sheets are called for demo purposes "site1", "site2", "site3" (but it can also reach 100) and in each one i used a data validation to add the workers that worked in each site from the "Source" list sheet
- i want to point out that all the sheets with "site 1 2 3" have same format (i just made one standard sheet which i clone everytime i have a new site) and in every sheet the names are shown in column A.

My original excel file is bigger with more then 20 sheets and its growing weekly.

Example:

Sheet "Source"
Workers
john
dan Danny
alex
marc
paul

Sheet site1
Worker
dan Danny
alex

Sheet site2
Worker
dan Danny
alex
marc
paul

Sheet site3
Worker
dan Danny
marc
paul


Sometimes errors are made when writing the names and i want to have the option that when i correct a name in the "Source" sheet (where is the list with all the workers names) to have that value automatically updated in all the sheets where that worker's name was used.

Example above: If by example i rename "dan" to "Danny" in the Source sheet "Workers", i would like also to be automatically updated in all the sheets (site 1, site 2, site3) where the old cell with the value "dan" was present so i don't have to do manually search and replace. If possible i would like also the code to work even if i add by example 100 more site sheets, to update any name change in all of them.


I found some examples online but they were just for two specific cases:
1. all the data in a single sheet (data validation + the list)
2. another one that used two sheets: a source and a second sheet with the DV but without instructions how to make the code work in multiple sheets.

I hope that i made myself clear and i didn't caused more confusions.

Thank you very much.
 

Attachments

  • test.xlsx
    14.9 KB · Views: 3
Last edited:
Hi, according to your attachment for starters paste this event provedure to the Source worksheet module :​
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
         Dim V, Ws As Worksheet
         If Target.CountLarge > 1 Then Exit Sub
    If Not IsEmpty(Target) And Not Intersect(Target, [workers]) Is Nothing Then
             V = Target.Value2
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
            .Undo
        If Not IsEmpty(Target) Then
            For Each Ws In Worksheets
                If Not Ws Is Me Then Ws.UsedRange.Columns(1).Replace Target.Value2, V, 1
            Next
        End If
             Target.Value2 = V
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Thank you very much for your time and fast reply.

I searched half of the day the internet for this and finally someone that really provided a solution!

I tested on my main excel file and it works like a charm!

I really appreciate it!

Thank you again for your help!
 
Back
Top