two-way links / references

N

neeta

Hi

Let's say I have a list of numbers, all in column A
I can create in column B a link to all those nums in col A (so that B1 = A1, B2 = A2, etc.

Now, if I change a cell in column A, the corresponding cell in B will also change. I call this a 1-way link

I would like a way so that if I change EITHER a number in column A OR one in column B, the other one updates
Does anyone know of a way to do this "two-way linking"? I want to actually have data where either one changing causes the other to change.

Thanks in advance..
 
F

Frank Kabel

Hi
this would<be only possible with VBA (using an event procedure). Not
possible with formulas.

For your example put the following code in your worksheet module (not
in a standard module). To get into the worksheet module right-click on
the tab name and choose 'Code'

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:B")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
application.enableevents = false
With Target
if .value <>"" then
select case .column
case 1
.offset(0,1).value = .value
case 2
.offset(0,-1).value = .value
end select
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub
 
Top