linking two cells that are bi-directional

[misscharliebrown]

Im using excel 2007 and I would really like to link two cells from same
workbook but different worksheets where if i enter information( text) into
the first cell (Sheet 1 A1)it reads in the second cell (sheet 2 B2) but if i
enter it into the second cell (sheet 2 B2) first it can be read in first cell
(sheet 1 A1). ive tried creating a circular reference but i find that once i
type in the text that it erases the formula.

 

[Mike H]

Hi,

You can't do that, if a cell contains a formula then that's it and
overtyping erases the formula. You need 4 cells. One the 'recieve' and one to
'send' in each workbook.

Mike

 

[Gord Dibben]

In sheet1 module enter this event code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Sheets("Sheet2").Range("B2").Value = Target.Value
endit:
Application.EnableEvents = True
End Sub

In sheet2 module enter this event code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("B2")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Sheets("Sheet1").Range("A1").Value = Target.Value
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP