Returning a status if one cell within a range meets criteria
Hi,
I am tracking some metrics using RYG balls. I have several parent metrics and child metrics under those all with RYG balls for a status. I would like the parent RYG ball to show red if at least one of its child metrics is red, yellow if at least one metric is yellow, and green if all are green. Does Smartsheet have this capability?
Thanks
Best Answer
-
Try this in the parent cell of the column with the status balls:
=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(CONTAINS("Green", CHILDREN()), "Green", "-")))
The last part of the formula "-" is just a value I put in situations like this since if you only have Red, Yellow, Green you should never see the final false value. If you see it you know you have a child cell that is either blank or some other value. Really you could make that whatever you want.
Answers
-
Try this in the parent cell of the column with the status balls:
=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(CONTAINS("Green", CHILDREN()), "Green", "-")))
The last part of the formula "-" is just a value I put in situations like this since if you only have Red, Yellow, Green you should never see the final false value. If you see it you know you have a child cell that is either blank or some other value. Really you could make that whatever you want.
-
I received an #INVALID COLUMN VALUE error
-
Oh I see what happened. It looks like this works now. Brilliant!
Thank you
-
Excellent! You're very welcome!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!