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

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,

Best Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    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.

  • Yonathan Meneses
    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 Cronk
    Mark Cronk ✭✭✭✭✭✭
    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.

  • Yonathan Meneses
    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 Cronk
    Mark 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 Cronk
    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


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


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

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


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

  • Sure!! I will do Thank you

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!