Circular Reference greyed out when sheet says errors

[Ochimus]

Opening the "real" file throws the message "Microsoft Excel cannot calculate a formula. There is a circular reference in an Open workbook, but the references that cause it cannot be listed for you."

The Ranges worksheet on the attached, based on the real file, states there are Circular references in row 2 of Cols H - J, but in FORMULA - FORMULA AUDITING - ERROR CHECKING, the Circular Reference is greyed out.

Col B is a dynamic range (Deductions) that populates the DropList in L8.
Dependent DropList in N8 needs to be populated by a dynamic range linked to the choice in L8.

Cols D - F contain dynamic ranges with the Deduction header and "_Ded" extension.
Cols H - J then have ranges with the formulae in row 2 "=FILTER(whatever_DED, whatever_DED <> "" so any blanks are ignored.
Range names for Cols H - J refer to row 2 in each case (e.g. "=RANGES!$H$2#" (asterisk ensures all the "spilled" row are included).

Both the Main and Dependent DropLists are working, but how do I remove the initial message and the circular references on the Worksheet, as I can hardly hand the workbook over "as is" to the End Users with the initial message on it?

All solutions, suggestions and alternative welcome as ever

Ochimus

[FDibbins]

Excel does it's best to try and identify where a circ ref is, and most times it works. However, when you have a more complex file with lots of inter-linked cells, excel takes its best guess...and often fails miserably.

My solution to this is to 1st SAVE the file so you have a fallback if you mess up!!

then...
delete the cell (or range of cells if they al have the same formula) that excel says contains the circ ref.
If that fixes the problem, then you have identified where the problem is, and know where to fix it. If it doesnt resolve the issue...

delete the cell (or range of cells if they al have the same formula) that excel says contains the circ ref.
If that fixes the problem, then you have identified where the problem is, and know where to fix it. If it doesnt resolve the issue...

rinse and repeat until the error message goes away. Make a note of the (last) formula cells involved, then CTRL Z (undo) to get back to original (or just close WITHOUT saving and reload)

Fix that last formula you found and see if that fixes it. If not, repeat the above steps again

Good luck, it can be a LONG and tedious process.

[Ochimus]

FDibbins,

Appreciate the prompt response and suggestion, but leaves me with a MAJOR headache, because the circular reference the sheet refers to are the cells "setting" the Dynamic Dependent DropLists.

If I delete those cells I'm right back to square one - HOW do I create a Dynamic Dependent list that links to a Dynamic Main list?

Ochimus