Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Determine RYG ball color based on rows below
Hello,
I'm trying to write a formula that'll allow me to look at the color of the RYG balls in some rows, find the lowest color and set that as the main color for the overall project.
So, Project A needs a status. I'd like to look at tasks 1-5; if any of them are red, the entire project status needs to be red. If any of the tasks are yellow, the entire project status needs to be yellow and if the tasks are all green, the entire project status needs to be green.
I started with this in the cell that represents the entire project status but I got an 'invalid operation' error.
=IF(Status2:Status20 = "Green", "Green").
Can someone please help?
Thank you,
Reema
Comments
-
You could definitely do this! I would use the COUNTIF function to count the number of each color and set the current cell appropriately. In this example, I'm setting the color of [Call Status]1 by looking at [Call Status]2:[Call Status]9
=IF(COUNTIF([Call Status]2:[Call Status]9, "Red") > 0, "Red", IF(COUNTIF([Call Status]2:[Call Status]9, "Yellow") > 0, "Yellow", "Green"))
Basically the formula goes in two steps:
1) If any of these rows is red, make it red. Otherwise, count the yellows.
2) If any of these rows is yellow, make it yellow. Otherwise, make it green.
Let me know if something isn't clear, or if that doesn't solve your situation!
-
That did it! Thank you Greg
-
worked for me too, thank you.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives