Creating Dependent Cells

by Allen Wyatt
(last updated August 28, 2021)

2

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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Creating Labels

Using Word to create and print labels is a snap. All you need to do is provide the text you want on the labels, pick a ...

Discover More

Putting Headers and Footers On Multiple Worksheets

You can easily create headers and footers for multiple worksheets by working with a selection set of the worksheets you ...

Discover More

Formatting a PivotTable

You can format PivotTables using either manual formatting or automatic formatting. You need to be careful, however, as ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Delaying in a Macro

At times, you may want your macro to pause execution. This tip presents four different ways you can add a delay into your ...

Discover More

Deleting Every X Rows

Grab some info from a source other than Excel, and you may find the need to delete a certain pattern of rows from a ...

Discover More

Hiding Macros

Need to hide some macros in your workbook? There are three ways you can do it, as covered in this discussion.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is five minus 3?

2021-08-28 09:52:36

J. Woolley

@Willy Vanhaelen
If you test two worksheets with both Worksheet_Change event handlers, I believe you will find the Application.EnableEvents code is necessary.


2021-08-28 07:51:43

Willy Vanhaelen

In both event macros, the Application.EnableEvents = False and Application.EnableEvents = True code lines, although they do no harm, they are superfluous because the cell being changed is not the Target cell. So, there cannot be an endless loop.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.