Cannot find circular reference

Post the file, remove any sensitivie data.

I don't know that there is much we can do without being able to see the file. I don't see that warning very often, but when I do see it, it is usually because the circular reference is across several tabs in the workbook or even across multiple workbooks. Perhaps an OFFSET() or INDIRECT() function is involved in the circular reference, making it difficult for Excel to identify where the circular reference is. Circular references that are "intermittent" like you describe might be due to IF() functions, where only one of the two output formulas contains the circular reference. Strategies I might use:

1) Turn on iteration, which will allow Excel to attempt to resolve the circular reference by computing it multiple times. In my version of Excel, I can usually see which cells are part of a circular reference as I can see them changing throughout the calculation process.
2) Once I have identified a cell that is part of the circular logic, then I can study that cell to see what it references and what references it.
3) If I can identify a "key" cell in the circular reference, then I might replace its current formula with the value it currently holds (if the cell currently resolves to 2, I will enter 2 in that cell to replace the formula). See what that does to the circular reference warning and calculation.
4) Keep working and trying these and other strategies until I find the problem.

I realize it may not be very helpful, but I'm not sure what more we can do without seeing the workbook.

Is there perhaps any way to see which cells are affected by the running iteration?

that was the whole point of suggestion 1 -- if any of the cell values are changing with each iteration, you can hopefully see them changing. It might be easier if you set max iterations to 1 while you are in there. then you can press F9 to go to calculate the next iteration and it might be easier to see the change.

As a "practice", you might look at the Barnsley Fern spreadsheet here: https://www.excelforum.com/tips-and-...eferences.html This spreadsheet intentionally uses circular references (and random numbers) to generate Barnsley Fern fractal pattern. It should allow you to test to see how you can see cell's changing during iterative calculation events. Again, if you set max iteration to 1, then you should be able to step through each iteration and see it change by pressing F9.

I have some 40+ sheets with 150+ charts/tables and a large datasheet with some 5 ths rows with many oracle-connection-based tables and pivot tables and additional calculations.

If at all possible, I would shrink this down. Charts are not part of circular calculations, so maybe delete them. Pivot tables do not contain any formulas, so they cannot participate in circular references (except maybe as source data for the formulas that are part of the circular reference). I am not familiar with how Oracle and Excel work together, but I might look at that process and see if it is even possible for the data importation step to be a part of the circular reference.

If it isn't obvious, you probably want to save the original workbook and not touch that file, but do all of this on copies of the original. In the event you end up in a dead end, you want to be able to go back to the beginning.