Linking cells to each other; having cells update linked cells and vice versa

Question

I have two ideas that could lead to more or less the same result.

I am trying to have similar cells or tables update themselves to whatever the most recent entry was in the linked system. For example, cell A1 is linked to cell B2 (in this system that I am trying to create). I would enter something like "1" or "Text" or whatever in A1 and the system would update cell B2 to whatever I entered in cell A1. However the opposite must work in the same manner. If I changed whatever in cell B2 to, say, "5", cell A1 would also display "5". Also, note that I have Excel 2013.

I need this to work with a cell or a table. So, getting to the possible solutions...

1. A subroutine in VBA that automatically updates all the linked cells or tables.

2. Some sort of mechanic unknown to me that uses VBA or another Excel aspect to do this, like a function or tool.

In your answer or solution, please be mindful to my inexperience with VBA. Thanks in advance.

Solution

You can try a worksheet change function:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("A1").Address then
       ActiveSheet.Range("B1").Value = ActiveSheet.Range("A1").Value
    ElseIf Target.Address = Range("B1").Address then
       ActiveSheet.Range("A1").Value = ActiveSheet.Range("B1").Value
    End If
End Sub

Although this seems like it might create an infinite loop (the update from the change causes another change) it DOES work for me in Excel 2010..

There are other Worksheet functions you can try as well (e.g. Worksheet_SelectionChange)

This macro needs to be placed/entered as a WORKSHEET macro on the sheet where you desire to use it..it will not work in a Module.

To install:

1) Save your workbook as a macro-enabled file.

2) Close Excel, reopen file, and enable macro security

3) Type Alt-F11

4) In the project explorer view on the left, look for your sheet name. Double click it

5) In the code entry area on right (big window) paste the example code above

6) Return to your worksheet and try it.

Comments

[David Zemens]

To avoid infinite loop, simply use Application.EnableEvents=False at the beginning of the procedure, and Application.EnableEvents = True at the end of the procedure. This ensures that changes made to the worksheet object during execution of this procedure do not trigger another _Change event, throwing you into infinite loop.

[henderso]

Great suggestion David!