VideoPhoto

23 August, 2021

How to create MUTUALLY DEPENDENT CELLS

Mutual dependence of Excel cells means that some two cells in your workbook refer to each other. If the value of one of the two dependent cells changes it causes change of value in the other cell dependent on it. This normally leads to a problem called "circular reference". However, it is possible to set up mutually dependent cells by following steps shown in my example below.

First, enable iterative calculation. Go to File>Options>Formulas (in Excel Options window) and select options as you see in this image:

Next, make sure that, depending on your needs, either Automatic or Manual calculation option is selected (go to Formulas tab>Calculation Options).

Now, enter these kind of formulas in your workbook:

  • in cell A2 of Sheet1:    =IFERROR(Sheet2!E2+15,"Error!")
  • in cell E2 of Sheet2:    =IFERROR(Sheet1!A2+F8,"Error!")

Underlined expressions indicate parts to be replaced by the contents of your specific cells that you want to designate as mutually dependent ones. Make sure that the result of such dependence meets your expectations.

Keep in mind that each time you save/update your workbook or press F9 key, the dependent cells get updated, because if e.g. the value of cell E2 changes, the value of cell A2 also changes. This could go indefinitely... Would such mutual dependence make sense for you? This is the question you need to answer.

No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.