Why sometimes a formula works and other times it shows #CIRCULAR REFERENCE?

Yonathan Meneses

I am working with this formula IF(ISBLANK([MoH ID]@row) = true, "", IFERROR(VLOOKUP([Val ID MoH]@row, {2.1 MoH Range 1}, 16, 0), "Error ID Trámite")). It worked for a few weeks and then suddenly when I open the sheet the formula result is #CIRCULAR REFERENCE. After few days, I opened the same sheet and the formula shows the correct result. Can you help me to understand why this is happening?

Thank you,

Mark Cronk

Hi @Yonathan Meneses ,

Are any of the ranges in your formula dates or date calculations? Some date calculations produce errors in Dec and Jan as the year changes.

Beyond that, I would delete the ranges in your formulas, save and then recreate them. See if that fixes the issue.

If you still have the problem it would be helpful to see a screenshot of your sheet and the referenced ranges.

Help?

Mark

Yonathan Meneses

Thanks Mark for the explanation. As it is working, in case the error shows up again I will send the the screenshots.

Thank you

Mark Cronk

Hi @Yonathan Meneses ,

Are any of the ranges in your formula dates or date calculations? Some date calculations produce errors in Dec and Jan as the year changes.

Beyond that, I would delete the ranges in your formulas, save and then recreate them. See if that fixes the issue.

If you still have the problem it would be helpful to see a screenshot of your sheet and the referenced ranges.

Help?

Mark

Yonathan Meneses

Thanks Mark for the explanation. As it is working, in case the error shows up again I will send the the screenshots.

Thank you

Mark Cronk

Glad you found a solution. Thank you for contributing to the Community.

Mark

Yonathan Meneses

Hi @Mark Cronk

Today I opened a smartsheet and the issue came up again. The formula worked correctly until today =VLOOKUP([Trámite ID]@row, {2. Estatus Range 1}, 5, 0), the formula is in the column highlighted below

Thanks for your comments.

Mark Cronk

Interesting. Can you send a screenshot of the column(s) used in  {2. Estatus Range 1}? Are you using formulas in this columns? If so, what formulas? Is [Trámite ID] a value or calculation? If calculation, what is the formula?

Mark

Yonathan Meneses

The range  {2. Estatus Range 1} has all the columns of another sheet. I created this range in this way to used it in different formulas in the same sheet in which I have the issue. So they referenced sheet has formulas in different columns, so far all them are working OK.  [Trámite ID] is entered manually, no formula.

Thanks for your support.

Mark Cronk

I would start by narrowing your range to just in life the columns you need. You can name each range to make referencing it easy. Can you try that and see if your issue resolves?

Mark

Yonathan Meneses

Thanks Mark, it worked as proposed. The strange thing here is that for a period of time ( months) the formula worked as set it up originally. Then, without a known reason, the #CIRCULAR REFERENCE is in the screen... do you know what would be the reason?

thank you

Mark Cronk

Hi Yonathan,

I don't even have a good guess. Let me know if it comes back and we'll try to figure it out.

Mark

Yonathan Meneses

Sure!! I will do Thank you