Is it possible to make a two way connection between two cells are in two different sheets? [duplicate]

I want to connect two of the cells between 2 Excel sheets, as well update both, if data has been entered in either of two cells.

• Cell A4 in Sheet 1.
• Cell B7 in Sheet 2.

Suppose, if 100 is entered in cell A4 in Sheet 1, then B7 in Sheet 2 should be updated with 100.

And if 80 is in cell B7 in Sheet 2, then cell A4 in sheet 1 should be 80 also.

I am using both version Excel 2013 & 2016.

Solution 1:

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 use the following VBA code in both the Sheets:

How it works:

• Either press Alt+F11 or Right click the Sheet TAb.
• Click View Code from the popup menu.
• Copy and Paste both in VB editor for respected Sheets.
• Save the Workbook as Macro Enabled.

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.