Random circular reference

Michael Culley

I have countifs formulas that work 99% of the time but every now and then when I login and look at the sheet, all of the forumlas display "CIRCULAR REFERENCE". I can't imagine that it is actually a circular reference so I'm wondering if it's perhaps a smartsheet bug. Any and all advice would be greatly appreciated.

 

Essentially I have a count if the owner is "Name" and if status is "Red". Again it always works fine but sometimes it randomly gives them all errors. I use this formula to show percent complete displayed by Harvey balls. 

Paul Newcome

When it throws the error, what steps do you have to take to fix it? Are there other people that are able to edit the sheet in any way?

Michael Culley

Everything is locked so no other users can change anything. I typically take no steps and the problem fixes itself in the past. Right now it's not fixing itself. The part I'm confused about is why its coming up as a circular reference in the first place. 

Andrée Starå

Hi Michael,

Have you looked in the Activity Log to see if there are some details about what's happening?

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Michael Culley

I'm noticing now that the problem seems to be in my countif formula that is counting RYG balls. I'm not sure why it comes up as a circular reference. 

Andrée Starå

What's the formula?

Michael Culley

It's in one of the pictures i attached but here it is again:

=COUNTIFS({ownership}, "BENITEZ, HOGUER", {new status}, "Red")

Andrée Starå

Ok.

I'd be happy to take a look and see if I can find the reason for it not working.

Can you describe your process in more detail and maybe share the sheet(s) or copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Michael Culley

None of the formulas show circular reference anymore. I'm pretty sure it's a bug with smartsheet. Doesn't make sense that it works fine for several months then suddenly shows circular reference, then suddenly doesn't show it. I haven't changed anything. Thanks for the help anyways.  

Andrée Starå

Excellent!

I'm always happy to help!

tfassnacht

I have the same issue.

 

I also agree it's probably a smartSheet bug.

It happens to me I think on Mondays (back-up is done during the w-e, that might be a cache issue, I don't know).

 

I also agree that my "Circular error" messages appear without any prior change on my side.

 

Best,

 

Hallie Brown

I've had this same issue for months and submitted multiple enhancement requests with no response. It hinders our operations when this happens.

Same as above, no material changes on our end. Seems to be triggered actually when a different piece of static data is changed in the sheet, almost as if the sheet is overloaded. However, there are less than 100 rows in that sheet, so that doesn't make sense to me. I don't do anything to the formula or source data, and it will randomly change back to the correct return value.

Does anyone have an idea of how to keep this from happening for cross sheet references?

manley85am

I too have had this error pop up a number of times. It always throws me off with the circular reference. Often it either resolves itself or I end up deleting and reconnecting the formula links (which is a pain) but that sometimes resolves the issue. This appears to be bug as this seems to happen without user input or changes.

I also would love to have some ideas on how to keep this from happening as our company VP's is looking at this information all the time and it's real embarrassing when metrics aren't correct.

Amy Mizzi

I wish I knew how to prevent it happening in the first place, but this is a relatively quick fix that has worked for me:

• Double click in a cell to edit the formula
• Click on the cross-sheet reference (for me, it's the lookup table in my VLOOKUP)
• Click "Edit Reference"
• Change the "Sheet reference name" (I just add 1 digit to the end)
• Click "Update Reference" and save sheet.

This fixes all formulas on the sheet which are using that same cross-sheet reference.

Craig Johnson

@Amy Mizzi

For some strange reason that worked! Thanks for the insight.

Craig

Susan Vieira

That fix worked for me as well. Thank you @Amy Mizzi

It's frustrating that this happens in the first place and I wish Smartsheet would acknowledge it. I sent them feedback as well.