Hello,
I need help to link cells dynamically between two sheets.
I found the answer below, but it is not quite on the scale I need it.
If I provided a list of cells (200 pairs) to link could that be quickly formatted into a VBA code that would link them all, without going through this process for each pair?
For example if I provided a list like:
Thank you!
B
----------------------------------------------ANSWER FOUND----------------------------------------------------
If you want a Dynamic link between Cells or Ranges of Cells, between Worksheets or even the Workbooks, for both way data entry, then VBA is the only solution.
You need to write the following VBA code in both the Sheets:
In Sheet 1:
Private Sub Worksheet_change(ByVal Target As Range)
If Not Intersect(Target, Range("A4")) Is Nothing Then
If Target = Range("A4") Then
Sheets("Sheet2").Range("B7").Value = Target.Value
End If
End If
End Sub
In Sheet 2:
Private Sub Worksheet_change(ByVal Target As Range)
If Not Intersect(Target, Range("B7")) Is Nothing Then
If Target = Range("B7") Then
If Sheets("Sheet1").Range("A4").Value <> Target.Value Then
Sheets("Sheet1").Range("A4").Value = Target.Value
End If
End If
End If
End Sub
NB: Code in Sheet 2 will update the Sheet 1 if the values in both cells are not similar. If you don't need this, simply remove the If statement line.
I need help to link cells dynamically between two sheets.
I found the answer below, but it is not quite on the scale I need it.
If I provided a list of cells (200 pairs) to link could that be quickly formatted into a VBA code that would link them all, without going through this process for each pair?
For example if I provided a list like:
| A | B |
| Sheet1!A1 | Sheet2!F26 |
| Sheet1!A2 | Sheet2!F36 |
| Sheet1!A3 | Sheet2!F46 |
Thank you!
B
----------------------------------------------ANSWER FOUND----------------------------------------------------
If you want a Dynamic link between Cells or Ranges of Cells, between Worksheets or even the Workbooks, for both way data entry, then VBA is the only solution.
You need to write the following VBA code in both the Sheets:
In Sheet 1:
Private Sub Worksheet_change(ByVal Target As Range)
If Not Intersect(Target, Range("A4")) Is Nothing Then
If Target = Range("A4") Then
Sheets("Sheet2").Range("B7").Value = Target.Value
End If
End If
End Sub
In Sheet 2:
Private Sub Worksheet_change(ByVal Target As Range)
If Not Intersect(Target, Range("B7")) Is Nothing Then
If Target = Range("B7") Then
If Sheets("Sheet1").Range("A4").Value <> Target.Value Then
Sheets("Sheet1").Range("A4").Value = Target.Value
End If
End If
End If
End Sub
NB: Code in Sheet 2 will update the Sheet 1 if the values in both cells are not similar. If you don't need this, simply remove the If statement line.
