AX1281

Circular references

Axiom Software displays a warning message in the Excel Client if it detects that circular references may exist in one of the open workbooks.

What is a circular reference?

A circular reference occurs when a cell in a worksheet refers to itself, whether directly or indirectly. For example, if you enter =1+D7 into cell D7, that is a circular reference because the cell references itself. Of course, most circular references result from more complicated formulas and references. You may need to trace a formula through many cells before finding the problem reference.

Why is the circular reference a problem?

In many cases, these references result from accidental errors in setting up formulas, and therefore should be corrected so that your formulas return the intended results.

However, even if the circular reference is intentional, the simple presence of a circular reference in any open workbook can cause Axiom's GetData function to not return data for all open workbooks. When a circular reference is present in a worksheet, it can cause a loop where the reference endlessly tries to resolve itself but cannot. The presence of this loop means that the calculation process of the workbook never gets to the point where the GetData function queries the database for its data. Therefore, the GetData function returns 0 instead of its actual result from the database.

NOTE: This GetData limitation only applies to the Excel Client. GetData functions will resolve as expected in the Windows Client, regardless of the presence of any circular references.

How can I find the circular reference?

Microsoft Excel provides a tool to locate circular references in a workbook. If the circular references warning has displayed, perform the following steps for each open workbook to find the circular references:

• Click the Formulas tab in the ribbon.

• In the Formula Auditing group, click the down arrow to the right of Error Checking to bring up the drop-down menu.

• If any circular references are present in the workbook, they will be listed under Circular References as shown in the following example screenshot. You can click the reference to be taken to that cell.

  

  If instead Circular References is grayed out, then no circular references exist in the workbook.

Once you have found the circular reference, you should review it to determine whether the formulas in the workbook need to be fixed to eliminate the circular reference, or if the circular reference is valid.

What if the circular reference is valid?

In some cases—usually in workbooks that contain very complex and sophisticated calculations—circular references may be intentional and valid. For these workbooks, Microsoft Excel supports a calculation behavior known as iterative calculation, which enables valid circular references to be resolved.

Kaufman Hall does not recommend enabling the iterative calculation option for Excel, because then it will apply to all files and may significantly slow down your system performance. Instead, Axiom provides several ways that you can specially process a file using iterative calculation. For more information, see Processing files that require iterative calculations.

If you have a workbook that contains valid circular references, then you should not leave this workbook open while you are working on other files in the Excel Client, due to the impact on GetData functions as discussed above. In this case you should think of the warning message as a reminder to finish whatever work you need to complete in the circular reference file, and then close the file as soon as you are done.