#circular reference error

Bret Houston

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

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.

Craig