#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?
Comments
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!