Creating Dependent Cells

Lance has two cells in a workbook: Sheet1!A1 and Sheet2!C3. He would like these cells to be dependent on each other so that a change in either of them results in a change in the other.

The answer, of course, depends on how the change is being made. If the change is due to a formula in each cell recalculating, then the answer is to simply modify the formulas so that that they are the same in each cell.

If the change is being made manually, meaning that someone enters a value into either cell, then the solution is to use a pair of macros to update the opposite cell. For instance, enter the following event handler by right-clicking on the tab for Sheet1 and choosing View Code:

Private Sub Worksheet_Activate()
    Worksheets("Sheet1").Range("A1") = Worksheets("Sheet2").Range("C3")
End Sub

Similarly, right-click on the tab for Sheet2, choose View Code, and then enter the following:

Private Sub Worksheet_Activate()
    Worksheets("Sheet2").Range("C3") = Worksheets("Sheet1").Range("A1")
End Sub

With these event handlers in place, whenever Sheet1 is activated, cell A1 will be set to whatever is in Sheet2!C3. Similarly, when Sheet2 is activated, cell C3 is set to whatever is in Sheet1!A1.

There is a downside to this approach—the values are only synchronized when a worksheet is activated. Plus, the synching can get out of whack. For instance, let's say you display Sheet2. At that point, cell C3 will be synched with Sheet1!A1. (So far, so good.) Now, you change what is in cell C3. However, Sheet1!A1 is not synched with that change because you have not yet activated Sheet1. If, at this point, you activate Sheet3 and then come back to Sheet2, your change in cell C3 is overwritten with the unsynched value from Sheet1!A1.

A better approach is to make sure the updates are done immediately. This can be done by, instead, using the following event handler in Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Application.EnableEvents = False
        Worksheets("Sheet2").Range("C3") = Range("A1")
        Application.EnableEvents = True
    End If
End Sub

This event handler fires every time there is a change in Sheet1. If the change is being made to cell A1, then that change is also updated in Sheet2!C3. To make the reciprocal arrangement complete, you need to use a similar event handler in Sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$3" Then
        Application.EnableEvents = False
        Worksheets("Sheet1").Range("A1") = Range("C3")
        Application.EnableEvents = True
    End If
End Sub

Even though this approach is better than the previous one, it still presents a potential gotcha. Let's say that you have the event handlers in place, and you are working in Sheet2. Now you enter a formula into cell C3. (Let's say the formula is =B6+B7.) Entering the formula triggers the event handler, and the result of the formula is copied to Sheet1!A1. Note that important caveat—it is not the formula that is copied, but the result of the formula. If, at this point, you change the value in Sheet2!B6, then the dependent formula in Sheet2!C3 is updated, but this value is not copied to Sheet1!A1 because the event handler doesn't recognize the formulaic change in Sheet2!C3. At this point, your two cells are again out of synch.

Of course, you could make the event handlers more complex, to check if a formula is entered into one of the cells. The problem is that you wouldn't want the handler to copy the formula from one cell to the other because you could very easily end up with erroneous results because the copied formula may not give the proper result once it is no longer in its original context.

Quite honestly, a better approach is to think through how you are using your data, with an eye toward data integrity. A good rule of thumb is that data should exist only in one place. If you need that data elsewhere, you refer to the original data. For instance, you might make Sheet1!A1 the "master" cell, the repository of the data. Then, in Sheet2!C3, you include a formula that refers to Sheet1!A1. This means you should not allow data entry into Sheet2!C3.

If this is not possible, then consider setting up a place in your workbook where a user enters data, in a single spot, and then both Sheet1!A1 and Sheet2!C3 can refer to that data-entry location using formulas. Thus, both cells would always be in synch with the data entered by the user, without the need for macros that can easily get your data out of whack.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10122) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021.