Overall Project Health (Parent Row) Formula based on Children Rows
Hello!
I am currently using this formula to visual task health.
=IF(OR(Status@row = "Complete", Status@row = "Done", Status@row = "Approved"), "Green", IF([Due Date]@row <= TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow", IF([Due Date]@row > TODAY(), "Green"))))
The tasks are nestled under a parent row for a given project. Is there a way to make the parent rows turn red if any of the tasks below are red? Right now they stay green since the overall project is still considered on time, however we want to be flagging a project if any of it's tasks are overdue.
In this example, I would want the project "Magnifica" to be red since some of the child rows are red.
Appreciate any help here!
Best Answer
-
Hi @Hannah Gray,
This is doable - if you only want the parent to be red if any of the children are, but green otherwise then this column formula in Health should work:
=IF(COUNTIF(CHILDREN([Health]@row), "Red") > 0, "Red", IF(COUNTIF(CHILDREN([Health]@row), "Green") > 0, "Green", IF(OR(Status@row = "Complete", Status@row = "Done", Status@row = "Approved"), "Green", IF([Due Date]@row <= TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow", IF([Due Date]@row >= TODAY(), "Green")))))).
If you want the parent to be yellow if there are no reds, but at least one yellow:
=IF(COUNTIF(CHILDREN(Health@row), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(Health@row), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(Health@row), "Green") > 0, "Green", IF(OR(Status@row = "Complete", Status@row = "Done", Status@row = "Approved"), "Green", IF([Due Date]@row <= TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow", IF([Due Date]@row >= TODAY(), "Green")))))))
Examples, with the first formula in Health column, second in Health2:
With a red status added:
Hope this helps - if you have any problems or questions with this let us know. 😊
Answers
-
Hi @Hannah Gray,
This is doable - if you only want the parent to be red if any of the children are, but green otherwise then this column formula in Health should work:
=IF(COUNTIF(CHILDREN([Health]@row), "Red") > 0, "Red", IF(COUNTIF(CHILDREN([Health]@row), "Green") > 0, "Green", IF(OR(Status@row = "Complete", Status@row = "Done", Status@row = "Approved"), "Green", IF([Due Date]@row <= TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow", IF([Due Date]@row >= TODAY(), "Green")))))).
If you want the parent to be yellow if there are no reds, but at least one yellow:
=IF(COUNTIF(CHILDREN(Health@row), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(Health@row), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(Health@row), "Green") > 0, "Green", IF(OR(Status@row = "Complete", Status@row = "Done", Status@row = "Approved"), "Green", IF([Due Date]@row <= TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow", IF([Due Date]@row >= TODAY(), "Green")))))))
Examples, with the first formula in Health column, second in Health2:
With a red status added:
Hope this helps - if you have any problems or questions with this let us know. 😊
-
Worked perfectly, thank you!
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives