Circular Reference Error Not Appearing Initially, then appears later

Sunil Kadikar

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.

Filipe Soares

I fixed my problem. There really was a record that was being referenced to the same spreadsheet. After correcting this single record the problem was solved.

Ben Goldblatt

Hi @Sunil Kadikar,

Per the Formula Error Messages Help article, the #CIRCULAR REFERENCE is caused when something in a formula references itself.

It does seem strange that the data populates as expected first but then reverts to the error message. If you haven't done so already, I highly recommend reaching out to the Support Team, as they will be the best resource for troubleshooting.

I hope this helps!

Thanks,

Ben

Filipe Soares

This problem is also happening to me.

Filipe Soares

I fixed my problem. There really was a record that was being referenced to the same spreadsheet. After correcting this single record the problem was solved.