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

01/23/21
Accepted

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,

Popular Tags:

Best Answers

  • Mark CronkMark Cronk ✭✭✭✭✭
    Accepted Answer

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Accepted Answer

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


    Thank you

Answers

  • Mark CronkMark Cronk ✭✭✭✭✭
    Accepted Answer

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Accepted Answer

    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 CronkMark Cronk ✭✭✭✭✭

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • 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 CronkMark 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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • 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 CronkMark 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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Sign In or Register to comment.