Circular Reference Error Not Appearing Initially, then appears later


I've created a few summary sheets which use VLOOKUPs from my master data sheet, as well as other sheets.

I've noticed that when I try to do a VLOOKUP, the formula will initially work, and pull the data as expected. However after some time, I'll re-open the same summary sheet, and see that the VLOOKUP Formula has an error message of #CIRCULAR REFERENCE.

I am pulling information into my summary sheet, and then also pulling information out of it back into the MASTER using VLOOKUPs, but neither of those VLOOKUPs are a circular reference, except for the fact that they reference the same range of cells. (but not the same columns)

More details below:

Master Data Sheet: (it's very large)

The "CHECK" Columns pull from the Summary Sheet that I created (second/third screenshot) and use a formula such as with a type of symbol:

=IF(ISERROR(VLOOKUP([COLUMN1]@row, {Summary Sheet Range 2}, 4, false)), "n/a", VLOOKUP([COLUMN1]@row, { Summary Sheet Range 2}, 4, false))

The "Summary Sheet Range 2" is all the columns in the below "Summary Sheet" screenshots.

Summary Sheet:

The Blue column contains the same value as the first column COLUMN1) above and references the above master sheet with a VLOOKUP to get the "OVERALL STATUS" and "PLANNED DATE..." values.

OVERALL STATUS and PLANNED DATE.. use formulas such as:

=VLOOKUP([KEYA]@row, {Master Sheet Range 1}, 17, false)

The "Master Sheet Range 1" is all the columns in the above screenshot)

The strange part is when I first put in the formula it works correctly (see below:)

But then a day (or so later) when I reopen the sheet, it will give me the error:

Any advice would be greatly appreciated.


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!