2

I know how to let the value of one cell depend on another cell (using = in the formula of the target cell).

However, is it possible to do an equal link, such that if I change the value of cell A, the value is immediately copied to cell B, but also if I change the value of cell B, cell A is also changed?

Basically it would be the "same" cell, but in two locations, so that data can be changed conveniently depending on in which worksheet you are.

3

You can do this using a VBA macro that traps the Worksheet.Change event. For example this code will keep the cells B2 and D10 on the same worksheet in sync:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Application.EnableEvents = False
    If Target.Address = "$B$2" Then
        Range("$D$10").Value = Target.Value
    ElseIf Target.Address = "$D$10" Then
        Range("$B$2").Value = Target.Value
    End If
    Application.EnableEvents = True
End Sub

In the VBA editor this code has to be stored in the worksheet on which you want to detect the change, not in the workbook or in a module. If you need the two cells to be on different sheets you'll need two macros, one for each sheet.

Note that we need to disable events before our macro changes the linked cell, otherwise that change will trigger another event and Excel will get stuck in an endless loop. Be aware that if your code exits with an error while events are disabled, you'll need to run a macro manually to re-enable them.

0

Select the B and white =A and you will have a mirror cell B. Changing the value of cell A will change the value of cell B immediately. Example:

example

  • 1
    That doesn't work in both directions, as the question asks for. – nekomatic Mar 13 '17 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.