Excel TODAY() Function causing Circular Reference error

Question

I've been pulling my hair out for hours trying to track down why all of a sudden my spreadsheet is broken, which uses the TODAY() all over the place. I've been all over the internet and tried all the solutions I could find, but it is still broken.

I see in the bottom left that there is a circular reference with an address, but the cell only contains: =TODAY(). If I delete it, it points to every other cell with a formula that uses TODAY(). If I hard code the date and remove the TODAY() function, it will point to the next one until eventually it even points to cells that don't use the function at all. Suddenly, I have simple sum formulas with 2 references that give me a value, but when I "enter" the cell and then hit the enter key to apply the calculation, the result goes to zero. Totally bonkers.

Currently using O365 on W10

Any help is appreciated.

Answer 1

The reference to the cell with =TODAY() is misleading. That is probably just the cell that the Excel calculation engine stopped at when it decided that there must be a chain of circular references. (I suspect it stops based on an iteration count.)

And many unrelated cells might return inexplicable zeros or Excel errors (e.g. #VALUE and #NAME) because the calculation engine aborted the process. Again, that is a misdirection.

Finding circular references can be difficult. As you know, a "circle" has no beginning or end.

Click Formulas > Error Checking > Circular References to find some cells that Excel believes are part of one or more chains of circular references.

If that option is greyed out, I suggest that you open the file in Manual calculation mode.

It is not necessary to save the file that way. Instead, close all windows and applications. Then click on the Excel program icon (not an Excel file) to start Excel. Then click Formulas > Calculation Options > Manual.

Then in the same instance of Excel, click File > Open (or Recent) to open the offending file. It will open in Manual mode because calculation mode is an application attribute, and we set Manual mode for Excel with the first file (Book1).

If that does not pan out, you might need to open Excel in safe mode. LMK if you need those instructions.

---

PS.... Do __not__ set Iterative Calculation mode unless you purposely have formulas that require iterative calculations. (A bad idea, IMHO.) Setting Iterative mode might eliminate circ refs. But that is just masking the problem, not fixing it.

Answer 2

do you have any recommendations on how I can chase it down from there?

[....]

when I click Calculate Now, then it gives me that popup and starts giving me false indicators. This makes me wonder if the culprit isn't some sort of cross sheet reference. What a mess. I'm even using named ranges and now I'm wondering if I need to check those out

It sounds like you're making progress. Unfortunately, except for "simple" circ refs, chasing them down is more art than science, as you've discovered. In my experience, the best person to do that is someone (you) who has intimate knowledge of the design.

I'm afraid it is unlikely that I can contribute anything, especially if the problem crosses worksheets, as you suspect. And named rerferences complicate things even more.

I am not aware of any tools that can help with this. But I just found something (click here) with a google search. You might take a look at their video to see if seems like a viable option for you. That said, beware of false and misleading claims. And obviously, I am not endorsing the product.

If you wish, you could provide the file for me to look at. Sometimes my methodical approach uncovers problems that others miss. But some caveats: (a) the file must be backward-compatible with Excel 2010; and (b) the file cannot have any external references.

Ideally, upload the file (redacted) to a file-sharing website, and post the download URL in response here. I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum. But IMHO, the download URL should not allow others to edit the file. That avoids accidental (as well as purposeful) changes. I'm all thumbs. (sigh)

If that is not a viable option, we could make arrangements for you to send the file to me directly. LMK.

Answer 3

I think I may have isolated the problem. I created a copy of my workbook and started overwriting formulas with the values (F2, F9, Enter). While in Manual Calculation mode, I started with these steps:

Calculate Now (click through message)

look for circular reference address in lower left

go to cell & overwrite formula

Calculate Sheet (clears the circular reference error)

Calculate Now

Rinse & Repeat until hitting Calculate Now doesn't give you the messagebox

Overwriting F11 with the value suddenly stopped the circular reference nightmare, but now I need to know how to resolve, because it isn't obvious. F11 has the following formula: =SUMIF(tbl_Projections[Date],"<=" & $E$11,tbl_Projections[Amt_AmexCC]). tbl_Projections has a [Date] column that starts with today and increments for each day. I have planned expenses in another column [Amt_AmexCC]. The goal of the formula is to return the sum of all expenses up to Oct 7th, which is the E11 value

I traced Dependents of F11 and it is only referenced by the cell directly beneath it, which merely adds it to another cell. So, I started tracing the dependents of the dependents, systematically overwriting the value, testing for errors and if there was not an error, I did an Undo to get my formula back, traced dependents, and continue on until I finally found the culprit. Boy, I really went for a ride on this one lol.

Of course, all it took was a simple adjustment and it was fixed, but tracking it down was a bear. My mistake, but i think Excel could have been a lot more helpful.

Answer 4

Thank you for the reply. I successfully performed your recommended steps of setting calculation mode to manual and then opening the file. I see no Circular Reference error until I actually calculate something and then it pops up again with a message saying it cannot calculate a formula because there is a circular reference, but the references causing it cannot be listed. It tells me to edit the last formula or using Undo, but there is no undo history now and I'm not sure where this took place.

I can close and reopen in manual mode, but do you have any recommendations on how I can chase it down from there?

With it in manual mode and before clicking "calculate now", I systematically went through each cell on each sheet I could think of and "entered" the cell and then hit the enter button to make sure it didn't trigger any warning. I also went to each sheet and clicked "calculate sheet" with no issues. During this process, I could enter cell J17 and type =J17 and it won't bug me about it and will just return a 0. When I hit calculate sheet, it will notify me and even give me an indicator under the Formulas > Error Checking section. If I clear the J17 cell, then Calculate Sheet, I have zero indicators. I can calculate each sheet and not have a single error or indictator, but when I click Calculate Now, then it gives me that popup and starts giving me false indicators. This makes me wonder if the culprit isn't some sort of cross sheet reference. What a mess. I'm even using named ranges and now I'm wondering if I need to check those out