#circular reference error


I'm trying to use a formula to set a symbol column's (RYG Balls) cell color value based on a date cell, however, if the cell color has been manually set to Green I don't want the formula to do anything.  For example, the cell in the formula below will default to Yellow. If it has been set to Green by the user, I want the cell to remain Green, regardless of the date in the [Invoice Date] column

=IF([Current Column]@row <> "Green",IF(TODAY() - [Invoice Date]@row > 30, "Red", "Yellow"))

How can I get around the #circular reference error on this?


  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    You can not manually change the cell without a formula without removing the formula. To resolve this, I have two columns:

    The automated version.

    The user override version.

    The automated version checks if there is override and uses that instead of the rest of the formula.

    =IF([user status]@row ="Green","Green", automated-status-check here)

    Lots of use cases for this:

    New orders are assigned to current quarter automatically for planning/tracking purposes but 1/10 or so are future orders that need to be manually assigned.

    At Risk state is determined by a set of rules but has a "new risk type" override for the user to flag it.

    I hope this helps.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!