Circular Referance

[John Vickers]

Hey, I don't know if this is possible or even where the post should be,
but here is what I want to do. Is there a way to have 2 cells that
reference each other, so that when you type in one, the value shows up
in both cells. The catch is that I want to be able to enter data in
either cell, so you can't have a standard reference link. Any ideas on
how to acomplish this?

John Vickers

 

[Guest]

Hi John,

this can only be done by a VBA module - afaik.

Assuming those two cells are A1 and G1 - try to use this code for the
Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = ActiveSheet.Range("A1") Then
ActiveSheet.Range("G1") = Target.Value
ElseIf Target = ActiveSheet.Range("G1") Then
ActiveSheet.Range("A1") = Target.Value
End If
End Sub

Did this help you?

Best wishes,
Eric

 

[John Vickers]

OK, I got that to work fine, and it does what I want. Here is the next
step, I will be populating a list from a macro in the module, and I
will need to create these linked cells dynamicly. Do you know of a way
to write a macro from a macro? In other words, add sets of linked cells
like you show here depending on the list it is populated from. So say I
have a list of 4 rows of data, when I populate the sheet, I would need
4 sets of linked cells, but if the list has 5 rows of data, then there
would need to be 5 sets of linked cells. The formatting will be to a
particular format, so I can do the programming of that, but how can I
add to the linked cells from a macro?

John Vickers