How to update the ancestor/parent rows with symbol colors that reflect child rows using a formula?
Hi
I would like to have the parent row have a formula that feed up from the child rows (the child rows have their own formula that changes status color depending on the date). so for example:
GREEN = In Progress
YELLOW = Attention
RED = At Risk
BLUE = Completed
-The Overall Status (parent row) is GREEN if all subcategories (child row) are green or GREEN with BLUE, but if 1 or more subcategories turn to YELLOW turn the overall status to YELLOW, but if 1 or more subcategories turn to RED turn the overall status to RED, but if all the subcategories turn to blue turn the overall status to BLUE
And then I would like to have the ancestor row have a formula that feed up from the parent rows, following the same rules as the previous ones.
Thank you in advance
Best Answer
-
This formula should do what you need:
=IF(CONTAINS("RED", CHILDREN()) = true, "Red", IF(CONTAINS("YELLOW", CHILDREN()) = true, "Yellow", IF(CONTAINS("GREEN", CHILDREN()) = true, "Green", "Blue")))
Tested and working, formula should be applied to the parent and ancestor rows in the Overall status column.
Hope it helps
Thanks
Paul
Answers
-
This formula should do what you need:
=IF(CONTAINS("RED", CHILDREN()) = true, "Red", IF(CONTAINS("YELLOW", CHILDREN()) = true, "Yellow", IF(CONTAINS("GREEN", CHILDREN()) = true, "Green", "Blue")))
Tested and working, formula should be applied to the parent and ancestor rows in the Overall status column.
Hope it helps
Thanks
Paul
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!