Circular reference to another file....

[yorkam]

Any ideas how I can find the error in a formula. I have a work book with lots of formulas in it and somehow through a crash or something now getting an error message

There are no links to any outside spreadsheets and the circular references that are identified in the sheet are ok and not circular references.. .any ideas??
Thanks in advance

 

[Joe4]

and the circular references that are identified in the sheet are ok and not circular references

What cells is it identifying?
Exactly what is in those cells?
If they contain formulas, please post those formulas.

 

[J.Ty.]

There is a method to find cyclic references even when the atuomated tool does not report them:
Cyclic references in Excel, Excel for Android and Excel online
The mothod is really to find cells which are not recomputed.

 

[yorkam]

for example one cell reference formula is =(MID(CELL("FIlename",$A$1),FIND("]",CELL("Filename",$A$1))+1,255)&$B35) to take name of tab and combine with cell reference

Another worksheet formula is='Core P&L'!D16 which references a worksheet which contains data based on the above cell

 

[Joe4]

=(MID(CELL("FIlename",$A$1),FIND("]",CELL("Filename",$A$1))+1,255)&$B35)

Which cell is this formula in?
What is in cell B35?

='Core P&L'!D16

What is in the cell D16 on the 'Core P&L' sheet?
Is it a hard-coded value, or another formula?

The main point is that sometimes the circular references aren't apparent.
It might not be explicitly in the formula that is references, but in some of the cells referenced in that formula.
So you need to check out all the cell references listed in the formulas (and if those formulas contain formulas, check those out too).

For example, what if in cell A1, you had the formula: =B1
By itself, it does not look like a circular reference. But what if B1 contained the formula: =A1?
Then you have a circular reference.

 

[J.Ty.]

If there is no progress, do the following:

1. Assume that you suspect that cell A1 is a part of the cycle.
2. Put somewhere a formula

   =IF(A1=A1,NOW())

3. If A1 is already a part of an active cycle, you will get 0 as the result, instead of the present time and date.
4. If it is not, you may put elsewhere a formula

   =NOW()

   .
5. If ever formulas from item 2. above and item 4. above give different results, then A1 has become a part of a cycle. You may apply conditional formatting to them to warn you about the difference.