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

Using List Box Controls

List boxes can be a great tool for getting input from users of your worksheets. This tip describes what list boxes are ...

Discover More

Getting Rid of Non-Printing Characters Intelligently

Is your worksheet, imported from an external source, plagued by non-printing characters that show up like small boxes ...

Discover More

Understanding File System Formats

When you format a disk drive, you have the option to specify what file system should be used on that drive. There are ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Recovering Macros from Corrupted Workbooks

Workbooks get corrupted from time to time; that's a fact of life in an Excel world. If those corrupted workbooks contain ...

Discover More

Generating Unique, Sequential Names

Do you need to create a number of words or phrases where you only alter a few letters in each one? If the alterations ...

Discover More

Out of Memory Errors for Macros

Tracking down memory errors in a macro can be frustrating. The error message is inherently vague and correcting any ...

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 six more than 1?

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.