Updating cell references across two worksheets

[Olsonic]

Good afternoon,

I have a situation that I would really like to resolve, and I'm really praying that someone in this forum could have some advice for me. Here is the situation:

I am using two worksheets.

Worksheet 1 contains the names of friends. This list of friends will be added to, and subtracted from over time (hopefully ) and it may also be sorted alphabetically, or by some other manner

Worksheet 2 contains tasks related to each friend


Question for Mr. Excel Users: For whatever reason, I may want to change the name of one of my friends. I may have spelled a name wrong, and want to re-name 'Mark' into 'Marc'. What I want to do is create a macro that, whenever I change one of my friends names in worksheet 1, will search through worksheet 2 for all tasks with the same misspelling and update them the same way. Is this possible?

Additional information​

• 
  [*=1]My list of friends on worksheet 1 will be added to and subtracted from, so the cell addresses will be changing
  [*=1]My list of tasks on worksheet 2 will also be added to and subtracted from, so the cell addresses will be changing.

 

[onlyadrafter]

Hello,

does this work as expected?

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Columns("A:A")) Is Nothing Then
        If Target.Value <> Range("F1").Value Then
            With Sheets("Sheet2")
                .Columns("B:B").Replace What:=Sheets("Sheet1").Range("F1").Value, Replacement:=Target.Value, LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=False
            End With
        End If
    End If
    Sheets("Sheet1").Range("F1").ClearContents
    Application.EnableEvents = False
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Columns("A:A")) Is Nothing Then
        Range("F1").Value = Target.Value
    End If
    Application.EnableEvents = True
End Sub

This code needs to go into the code window for the 'Friends' sheet tab, not a standard module.

It has no error checking though.

 

[Olsonic]

Hi There,

Thanks so much for your response. I haven't been able to get the macro to work yet, but I'm working on it. I'm confused about this line of code. Why are you using Range("F1") ?

If Target.Value <> Range("F1").Value Then

 

[AnalystJames]

This isn't a perfect solution when you have multiple friends with the same first name, but why don't you just use the find and replace advance options? Ctrl+F, replace tab, options, check within workbook.

 

[Olsonic]

ADVERTISEMENT

This isn't a perfect solution when you have multiple friends with the same first name, but why don't you just use the find and replace advance options? Ctrl+F, replace tab, options, check within workbook.

Because in reality, I have a customer user form that I use to update profiles of my friends, and then a different worksheet with tasks related to them. So when I make an adjustment to a profile via the UserForm, I want it to propagate to the other worksheets

 

[Olsonic]

Hello,

does this work as expected?

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Columns("A:A")) Is Nothing Then
        If Target.Value <> Range("F1").Value Then
            With Sheets("Sheet2")
                .Columns("B:B").Replace What:=Sheets("Sheet1").Range("F1").Value, Replacement:=Target.Value, LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=False
            End With
        End If
    End If
    Sheets("Sheet1").Range("F1").ClearContents
    Application.EnableEvents = False
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Columns("A:A")) Is Nothing Then
        Range("F1").Value = Target.Value
    End If
    Application.EnableEvents = True
End Sub

This code needs to go into the code window for the 'Friends' sheet tab, not a standard module.

It has no error checking though.

I know we're on to something here. The Worksheet_change and Worksheet_selectionchange appear to be the right way to go about this, but Im just getting all sorts of errors when I plug in that formula.

 

[Olsonic]

I've decided to add another example to see if this helps. I have two worksheets (Sheet1, Sheet2). As you can see, Sheet1 is a database of names and related information, and Sheet2 is a list of tasks related to some of the names in Sheet1. My problem is, if I make an adjustment to the names in Sheet1, the names of Sheet2 are not updated accordingly. Additionally, there is the added complication that I often sort the names in Sheet1 based on different criteria, so the exact cell location will be constantly changing. Whatever solution I use needs to be able to react to that, if possible. I'm close to giving up but I've put in so much **** effort into this database that I just can't believe I'm gonna stop here. Everything I've learned so far has been through YouTube, and it's just been impossible to find the right tutorial on Worksheet_change and Worksheet_SelectionChange.

Anyways, I'll quit posting on this thread so I don't look bad for bumping it. Just making a last ditch effort to get some eyeballs on it because it's important to me.

Thanks.

 

[onlyadrafter]

Hello,

what errors do you get?

Did spot a couple of errors, try

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Columns("A:A")) Is Nothing Then
        If Target.Value <> Range("F1").Value Then
            With Sheets("Sheet2")
                .Columns("A:A").Replace What:=Sheets("Sheet1").Range("F1").Value, Replacement:=Target.Value, LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=False
            End With
        End If
    End If
    Sheets("Sheet1").Range("F1").ClearContents
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Columns("A:A")) Is Nothing Then
        Range("F1").Value = Target.Value
    End If
    Application.EnableEvents = True
End Sub