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

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

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?




Sign In or Register to comment.