Circular References

A circular reference is just a formula that depends on its own value.
A circular reference is created if you enter a formula into a cell that links directly or indirectly to itself.
There should not be any circular references in your workbooks unless you specifically want them to be there.
If you receive a circular reference warning when you are not expecting one it means that the formula has been entered incorrectly.
These can be created very easily (often by mistake). Just link cell B2 to cell B3 and then link cell B3 to cell B2.

Normally circular references are created by mistake although there are a few occasions when they can be useful.
When an open workbook contains a circular reference this is indicated in the status bar.

The cell containing the circular reference is only displayed when the cell is on the active worksheet. - CHECK !!

Iteration

Iteration is the process of repeatedly calculating values in a worksheet until a specific numeric condition is met.
This enables you to have circular references and offers a method of controlling it.
You can enter a circular reference if the "Iteration" checkbox is ticked on your (Options)(Calculation tab).

Excel will continue to iterate until either a maximum number of iterations has been performed or until the values change by less than a given amount.
Circular references can be used to solve convergence problems because each time Excel recalculates, the results in the cells get closer and closer to a particular value.
Maximum Iterations - The default maximum number of iterations is 100.
Maximum Change - The default maximum change between the values is 0.001.
Despite this being an application setting it is saved into your individual workbooks.
These settings are taken from the first workbook you open and then ignored in subsequent workbooks. Please see Persistent Settings below for more information.

Calculation

If the word calculate appears in the status bar after the iterations are finished, more iterations are possible.
You can either accept the current result, increase the number of iterations or lower the maximum change amount.

For more information please refer to the Calculation page.

Using tracer arrows will help to identify all the cells that a particular formula refers to.
If you glance at the formula or the status bar you can pinpoint the exact cell that contains the error.

Persistent Settings

The Iteration and Calculation settings are workbook specific although Excel retrieves these settings from the first workbook that is opened.
These settings are not changed (by default) for any subsequent workbooks that are opened.
You can manually change these options for a workbook but there is no guarantee that the next time you open the workbook it will use those settings.
Please refer to the "Why does my calculation change to Manual" on the Calculation page.

If the Iteration checkbox is not ticked and you have the calculation mode set to Automatic then when you open a workbook containing a circular reference the warning message will be displayed.
You can prevent this by explicitly setting the Iteration checkbox value to ticked in code.
If you have a Personal.xls file then this is "always" the first workbook to be opened when you launch Excel and therefore it is this setting that will always be used.
This subroutine can be added to the ThisWorkbook module of your Personal.xls to make sure that whenever you open Excel the Iteration checkbox will be ticked.

Public Wbk_Open() 
   Application.Iteration = True 
End Sub 

Excel does not repeat the "cannot resolve the circular reference" error message if it fails to resolve the reference.

When linking cells between different worksheets and different workbooks although try and have a ?? as opposed to random linking.
Also be aware that if cells are linked / references and the original cell is deleted then #REF appears in all the original formulas that referenced that now "deleted" cell.
When copying links to an external market data source between spreadsheets with the same name you must rename one of the workbooks and open them both up together.
It will not work by copying then closing and opening the new one.

Important

Some circular references are useful (in the case of convergence) and some are even essential in certain situations.
Circular references can be useful when you want a value to converge.
Once the circular reference error has been dismissed it will not appear again until the worksheet is recalculated.
You cannot reference its own value in a function either by cell reference or in VBA - cannot make a reference to it at all