Circular Reference Issue

[ronie85]

I'm hoping someone can help.

I moved a few sheets from one workbook to another. The previous sheets had formulas which I think I had corrected them all within the new workbook to relate to places within the new workbook opposed to the previous.

Now I keep getting the below error message and I cannot find the issue cell.

The bottom of the workbook shows this however the cell keeps changing and there isn't actually anything in the shown cell therefore it is incorrect.

Lastly when I click on Formulas and Check Errors, it doesn't show any circular reference errors.

Would anyone know an easy way to find exaclty where this issue is originating from so that I can rectify it?

 

[Jeffrey Mahoney]

You may have copied Named Ranges from the other workbook. Check for duplicate named ranges; one of them may be a SHEET scope and the other may be a WORKBOOK scope.

 

[ronie85]

You may have copied Named Ranges from the other workbook. Check for duplicate named ranges; one of them may be a SHEET scope and the other may be a WORKBOOK scop

Do you mean search the entire workbook? If so, there were no results for SHEET or WORKBOOK. Can you advise any otehr way to check for duplicate name ranges?

 

[Jeffrey Mahoney]

Click on Formulas > Name Manager. Also look for references to other workbooks. You may want those and you may not. In the Scope column you may see Sheet names or Workbook. Duplicate named ranges with two different scopes confuse Excel.

 

[ronie85]

I had 2 inputs in Name Manager, they weren't required and I don't know where they came from. I have since deleted them though the circular reference dialog box is still coming up

 

[Jeffrey Mahoney]

Make a copy of your workbook. One sheet at a time, copy all formulas and paste as values until you don't see the circular reference any more. Then once you find the sheet, undo the paste values and start doing the same thing for smaller sections of formulas on that sheet until see the circular reference go away.

 

[ronie85]

Make a copy of your workbook. One sheet at a time, copy all formulas and paste as values until you don't see the circular reference any more. Then once you find the sheet, undo the paste values and start doing the same thing for smaller sections of formulas on that sheet until see the circular reference go aw

I found it eventually! Formula in one sheet that was referencing another sheet. The other sheet had a Unique Sort formula which relied on the first sheet.