#CIRCULAR REFERENCE auto corrects after I copy and paste the exact same formula.

EmilyH

I have theories as to why this is occurring but, for now, here is what happened. One of the sheet formulas looks something like this:

=IF([COLUMN A]1 = "YES", INDEX({Smartsheet 1 Column B}, MATCH([PROJECT#]1, {Smartsheet 1 Column A}, 0)), IF([COLUMN A]1 = "NO", INDEX({Smartsheet 2 Column B}, MATCH([PROJECT#]1, {Smartsheet 2 Column A}, 0))))

In other words, if YES > go to Smartsheet A and get data. If NO > go to Smartsheet B and get data.

On Friday January 24th, the formula worked, no errors. However, on Monday January 27th the formula result displays a #CIRCULAR REFERENCE error. The first IF statement pulls data from Sheet 1 but when I remove the first IF statement and only search for the NO results in Sheet 2, I still get the #CIRCULAR REFERENCE error.

On Monday, how did I fix the #CIRCULAR REFERENCE error? I copied and removed the formula in one cell. Then I paste the formula (exact same formula). Save, then all column data is restored. I did not change or update any cross-sheet formulas.

The problem is that we track changes. All the columns that are affected by the random error appear as updated or new entries.

Today is Tuesday January 28th and there are no errors, the formula works. I am sure next Monday, the #CIRCULAR REFERENCE error will appear again (fingers crossed it will never happen again). Is there a weekend or biweekly system refresh that could be removing cross-sheet references? This is a mystery to me, but it has happened a few times over the past few months. I receive emails from frustrated data entry personnel because the referenced data is missing.

When it happens again, I will add a new comment to this Community discussion.

Thank you.

EmilyH

It happened again February 5th, 2020. Today I fixed the issue by:

1.    The #circular reference error appeared in many rows and columns:

=LEFT(SUBSTITUTE($[JOINED DATA]@row, JOIN($[PROJECT#]@row:[PROJECT#]@row, "/") + "/", ""), FIND("/", SUBSTITUTE($[JOINED DATA]@row, JOIN($[PROJECT#]@row:[PROJECT#]@row, "/") + "/", "")) - 1)

2.    Changed one digit in one formula for one cell and saved:

=LEFT(SUBSTITUTE($[JOINED DATA]@row, JOIN($[PROJECT#]@row:[PROJECT#]@row, "/") + "/", ""), FIND("/", SUBSTITUTE($[JOINED DATA]@row, JOIN($[PROJECT#]@row:[PROJECT#]@row, "/") + "/", "")) + 1)

3.    Changed the one digit back to be the same as the original formula that created the #circular reference error:

=LEFT(SUBSTITUTE($[JOINED DATA]@row, JOIN($[PROJECT#]@row:[PROJECT#]@row, "/") + "/", ""), FIND("/", SUBSTITUTE($[JOINED DATA]@row, JOIN($[PROJECT#]@row:[PROJECT#]@row, "/") + "/", "")) - 1)

4.    All cells in all columns with the #circular reference error were fixed and the data reappeared.

The formula works. But for an unknown reason, we keep getting random errors.

JLC

@EmilyH, I just had the same thing happen yesterday. I have a series of formulas that use a cross-sheet reference to a single cell. This single cell contains, simply, "=TODAY()". We have it built this way as the cell is in a sheet that is updated daily, so we never have to worry about "TODAY" not refreshing for use in our dashboard metrics.

Yesterday, all of a sudden, our series of formulas using this cross-sheet reference all threw a #CIRCULAR REFERENCE error. I cut the TODAY formula completely, saved the sheet, pasted it back in, saved the sheet, and the formulas referring to that cell all corrected themselves.

Smartsheet Support was (understandably) unable to duplicate this issue and as such asked me to record my screen next time this happens. Here's hoping it never happens again as it resulted in multiple executive dashboards showing a significant decrease in their metrics!