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
-
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.
Answers
-
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
-
This problem is also happening to me.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!