Creating Dependent Cells

Written by Allen Wyatt (last updated August 28, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


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 Microsoft 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

Punctuation Marks that Don't End Sentences

Word tries its best to recognize when you've reached the end of a sentence and then helpfully capitalizes the first ...

Discover More

Returning an ANSI Value

Need to know the character value of the first character in a string? It's easy to do, without using a macro, by using the ...

Discover More

Setting Default Attributes for Lines and Arrows

Don't like the way that Excel formats lines and arrows? You can easily make your own formatting changes, and then use ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Disabling Shift Key Use when Opening a Workbook

Open up a workbook, and Excel normally runs the macros associated with that workbook. You can disable the automatic ...

Discover More

Debugging a Macro

Part of writing macros is to make sure they work as you expect. This involves a process known as debugging. Here's how ...

Discover More

Recording a Macro

One of the most common ways of creating macros is to use Excel's macro recorder. This tip shows how easy it is to use the ...

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 2 + 8?

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.