Update multiple columns (from different worksheet) if a column is updated

[erin00]

I have a spreadsheet with multiple sheets.

I have two columns in sheet1 (Let's say it's column A and C). Column A and C are related (A is name, C is Status)

Some of the values in column A can be found in Sheet2 (say column A as well). Some of the values in column A can also be found in Sheet3 (say column A as well). So values in sheet1-A can be found in sheet2-A and/or sheet3-A.

Let's say a value in sheet1-C is changed/updated.

I want to look at sheet1-A and find if it has a match from sheet2-A and sheet3 C.

If it find a match, it should be able to update the value of its corresponding column C.

 

[JLGWhiz]

So if an change occurs in column C of sheet 1 sheets 2 and 3 should be queried to make corresponding updates using the same columns as found in sheet 1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim fn As Range
If Not Intersect(Target, Range("C:C")) Is Nothing Then
    Set fn = Sheets("Sheet2").Range("A:A").Find(Target.Offset(, -2).Value, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            fn.Offset(, 2) = Target.Value
        End If
    Set fn = Sheets("Sheet3").Range("A:A").Find(Target.Offset(, -2).Value, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            fn.Offset(, 2) = Target.Value
        End If
End If
End Sub

This assumes there are no duplicate entries in column A of any sheet. the code should be copied and pasted into sheet 1 code module. To access the code module, right click the sheet name tab and click 'View Code' in the pop up menu. After installing the code, clode the VB Editor window and save the workbook as a macro enabled workbook, if it is not currently. The code will execute when changes are made in column C of sheet 1.

 

[erin00]

Hi JLGWHiz,

Thanks for this.

Just another question, the columns don't really have the same number of rows. Some items in Sheet 1 can be found in Sheet2 but not with Sheet3.

So for example, Sheet1-columnA have 10 rows. On the 10th row, there is a match in Sheet2, but sheet2 only have 5 rows. THe code above still puts the value on the 10th row. It should be able to update the cell where the desired value is found.

 

[erin00]

Apologies. I was wrong with my comment above. But I will test this again. Thanks.

 

[JLGWhiz]

The number of rows should not matter with the code provided. It searches sheets 1 and 2 for a match of the value in column A where the change is made on sheet 1. If a match is found, it then makes the cell in column C equal to the value of the cell that was changed on sheet 1. The only anomaly might occur if there are duplicate values in Column A of sheets 2 or 3.

 

[erin00]

If it is not too much, can you explain me the code above?

The new value is not being placed on the right cell (it should be on the same row where the columnA value is found). And also it still updates the other sheet even if the value from sheet1 is not present on the specific sheet.

 

[JLGWhiz]

If it is not too much, can you explain me the code above?

The new value is not being placed on the right cell (it should be on the same row where the columnA value is found). And also it still updates the other sheet even if the value from sheet1 is not present on the specific sheet.

See post #5.

If you retyped the code rather than copy and paste it, there could be typos causing your data to post in the wrong place. Check the Offset statements to make sure there is a comma before the number in parentheses.

 

[JLGWhiz]

Just re-read your OP and saw this. Try this one.

I want to look at sheet1-A and find if it has a match from sheet2-A and sheet3 C.

The current code does not do this, it only checks for a match to column A of Sheet 1 to col A of both the other sheets.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim fn As Range, fn2 As Range
    If Not Intersect(Target, Range("C:C")) Is Nothing Then
            Set fn = Sheets("Sheet2").Range("A:A").Find(Target.Offset(, -2).Value, , xlValues, xlWhole)
                    If Not fn Is Nothing Then
                            Set fn2 = Sheets("Sheet3").Range("C:C").Find(Target.Value, , xlValues, xlWhole)
                                     If Not fn2 Is Nothing Then
                                             fn.Offset(, 2) = Target.Value
                                     End If           
                    End If   
    End If
End Sub

 

[JLGWhiz]

Post #8 still might not be right. Maybe this one.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim fn As Range, fn2 As Range
    If Not Intersect(Target, Range("C:C")) Is Nothing Then
        Set fn = Sheets("Sheet2").Range("A:A").Find(Target.Offset(, -2).Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                Set fn2 = Sheets("Sheet3").Range("A:A").Find(fn.Value, , xlValues, xlWhole)
                    If Not fn2 Is Nothing And fn2.Offset(, 2).Value = Target.Value Then
                        fn.Offset(, 2) = Target.Value
                    End If
            End If
    End If
End Sub

This seaches sheet 2 for the value in column A of sheet 1 where a change occured in column C. If a match is found in sheet 2, it then seaches for a match in column A of sheet 3 and compares column C value on sheet three to the change value on sheet 1, if there is a match for sheets 1 and 3, then sheet 2 Column C is updated with the change. If that is not what you want, you will need to restate your objective to clarify what you want to match on which sheets.

 

[erin00]

The code is working in excel 2013 but not in excel 2010. Im not sure if that is the problem. Probably some settings in my excel 2010? any advice on where to check?