Excel Circular References – How to deal with them?

Introduction

If you’ve come to this page, chances are you’ve experienced the “Circular reference warning” popping up when you opened an Excel file or entered a formula. Excel detects a circular reference when a chain of calculations visits the same cell more than once. Many users get (very) confused by this message and have no idea what it is about. I’ll try to demystify that message here!

The next couple of pages discuss:

Table Of Contents

---

Reasons why circular references may not be detected

Error results

Sometimes, if cells within a circle have an Error result, the circle may no longer be detected by Excel as being a circular reference (unfortunately, I could not reproduce the problem when I tried to create a demonstration file)

Values affecting whether a circle is really working as a circle

If one of the cells inside a circle contains a function which may affect which arguments are in use (an IF function, a CHOOSE function, …), then whether Excel detects a circle depends on that function’s proceedings. This is what I used to create switch cells which can break your circles and set initial values.

Use of arguments in a UDF

Excel is smart. Very smart. Suppose you have a written a VBA User Defined Function (UDF) with two arguments. Excel is smart enough to detect which arguments are actually used in the calculation. So the argument values of the functions used in the circle may affect whether Excel decides it *IS* a circle, because one of the values might cause an argument of a function in the circle not to be used, hence potentially breaking the circle.

#Name error caused by missing UDF

If any cell within a circle contains a reference to a UDF that isn’t available, the circle is not detected.

Calculation status

When a workbook is opened, calc settings depend on whether or not another workbook is already open in Excel. If you FIRST open a workbook with iteration turned off and THEN open the file with circular references, the circular reference warning shows up. If Iteration is turned on for the first workbook, opening subsequent workbooks does not trigger the warning.

Table Of Contents: Go to

---

Courtesy: jkp-ads.com