Baffled......Circular / Blocked Reference (Vlookup)

scott.barrett

This one is baffling.  I have a problem with 2 sheets reading each others data through a Vlookup.  Each sheet has Vlookup ranges from the other sheet.  I understand why formula and Vlookup may return a #circular or #blocked reference, but I don't understand why my sheets work perfectly for periods of time, and then start returning #circular or #blocked data without any changes to the sheets.  Follow the bouncing ball.

1. Sheet 2 (schedule) reads data from sheet 1.
2. Sheet 2 (schedule) runs dates based on dependency setting.
3. Sheet 1 reads the resulting data from Sheet 2 (schedule).
4. Sheet 1 has columns with "IF" statements reading the data in other columns on Sheet 1 that are returned from Sheet 2 (schedule).

A few things are happening that I can't explain.

• The 2 sheets are a little slow due to the Vlookup and formulas, but this is expected and the reports to teammates take care of that problem.
• The 2 sheets work perfectly together and all data is easily obtained without problems.
• Without a known "cause" or any perceived action, one of the two sheets (never both) will start displaying #circular or #blocked in the cells with the Vlookup range.
• On occasion it feels like this happens as soon as I share the sheets (viewer only) with others.  Other times this will occur in the morning when nobody has even been viewing the sheets or reports overnight.
• If I go in and change the Vlookup range "Name" (without changing the actual range), it appears to fix the issue after some time passes.  I can simply change the range name from "Shop Schedule Range 1" to "Shop Schedule Range 2" and keep the exact same range.  It will sometimes return the data immediately, and sometimes it takes up to 30 minutes or an hour to repair itself.

 

Anyone have any ideas?

 

Scott

Shaine Greenwood

Hi Scott,

Please submit a case to our Support team to troubleshoot this issue further. They'll work with you, and gather more information on your setup to provide next steps.

Martin V

I get the same circular / blocked errors and it's infuriating. 

Hallie Brown

Is there any solution for this open to the general public?

Michael Culley

I discovered that If 2 sheets are referencing eachother with vlookups on smartsheet, none of the ranges can include a circular reference.

So if Sheet1 has a formula that says vlookup(row1@row, {Sheet2 columns 3 through 6}, 6, false) and Sheet2 has a vlookup formula in lets say column 5 that references sheet1, it will return a circular reference. Even if the vlookup formula is not referencing column 5, column 5 is still in the range of the vlookup range on sheet 1.

I've solved this by creating hidden columns at the end of sheet1 that essentially copies columns 3-6 and I use my vlookup formulas on those.

Sorry if that was confusing but I thought it might be helpful because to me, that shouldn't be considered a circular reference.

Paul Newcome

It may also be helpful to look into INDEX/MATCH formulas to replace your VLOOKUPS. INDEX/MATCH only requires you to create references to the columns you are actually referencing and not any of the columns in between.

Additionally... The error that comes up and then goes away after some time... Is it "Circular" or "Calculating"?