RAG Roll-up formula
Could anyone help me with a formula I'm trying to perfect.
I am trying to create a formula that automatically captures RAG status for Children Rows and how this is communicated to the Parent rows.
The plan is essentially structured like this (images are attached):
- Project Plan Summary (Senior parent)
- Main Tasks (Parent)
- Activities (Children)
- Main Tasks 2 (Parent)
- Activities 2 (Children)
- Main Tasks (Parent)
The formula I am using to count the children activities to populate the RAG status for the "Main Tasks" is:
=IF(COUNTIF(CHILDREN(), "Red") >= 1, "Red", IF(COUNTIF(CHILDREN(), "Yellow") >= 1, "Yellow", IF(COUNTIF(CHILDREN(), "Green") >= 1, "Green", " ")))
*This works fine as I want Red to always be flagged up no matter what*
The formula I have for the "Project Plan Summary" is:
=IF(COUNTIF(CHILDREN(), "Red") >= (COUNTIF(CHILDREN(), "Yellow") + COUNTIF(CHILDREN(), "Green")), "Red", IF(COUNTIF(CHILDREN(), "Yellow") >= (COUNTIF(CHILDREN(), "Green") + COUNTIF(CHILDREN(), "Red")), "Yellow", IF(COUNTIF(CHILDREN(), "Green") >= (COUNTIF(CHILDREN(), "Yellow") + COUNTIF(CHILDREN(), "Red")), "Green", IF(OR(COUNTIF(CHILDREN(), "Red") = COUNTIF(CHILDREN(), "Yellow"), (COUNTIF(CHILDREN(), "Red") = (COUNTIF(CHILDREN(), "Green")))), "Red", IF(COUNTIF(CHILDREN(), "Yellow") = COUNTIF(CHILDREN(), "Green"), "Yellow", "Green")))))
The formula above is meant to pick the mode from the "Main Tasks" and if there are two then pick the worst case to be flagged up.
What I need help with in particular is that if there are no status symbols in the "Main Task" rows (i.e they contain " ") I want the "Project Plan Summary" to also be blank.
If anyone could offer some insight I would be very appreciative, even if you can't thanks for reading my long post.
Comments
-
Try something like this...
=IF(COUNTIFS(CHILDREN(), ISBLANK(@cell) = COUNT(CHILDREN([Primary Column]@row)), "", insert_original_formula_here)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!