How can I make a 2-way cross-link between cells from diff. worksheets?

Posted by Jeff kelley on December 15, 2000 9:06 PM

I would like A1 in worksheet 1 to be the "same field" as B2 in worksheet 2; changing one automatically changes the other (and all the Sum's, etc. that use them in their respective worksheets). I've been pouring over the help and FAQ's for a long time and haven't hit on the right keywords to find the answer.

Posted by Celia on December 16, 2000 1:49 AM

Put the following procedure in the ThisWorkbook module :-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target = Worksheets("Sheet1").Range("A1") Then
Application.EnableEvents = False
Worksheets("Sheet2").Range("B2").Value = Worksheets("Sheet1").Range("A1")
Application.EnableEvents = True
ElseIf Target = Worksheets("Sheet2").Range("B2") Then
Application.EnableEvents = False
Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet2").Range("B2")
Application.EnableEvents = True
End If
End Sub