Automating a Roll up for RYG Symbols for Health

NCharleb

Team,

Thank you for your patience, I'm still learning.

I have gone down a rabbit hole trying to figure out how to roll up Health symbols RGY. In my search for help I came across this formula but it's not working. =IF(COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 1, "Yellow", "Green")))

In the example below I want a formula to automate the Health symbol to roll up to Treasury, another for Trust, and then overall for Finance that would include the last 5 tasks.

Kelly Moore

Hey @NCharleb

I combined the Parent Row Rollup formula with the Non-Parent row formulas. The formula first looks for Parent rows by seeing if any children are present (Count of children If yes, it applies the parent formula. If false, it applies the non-parent formulas. See if this works for you

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 1, "Yellow", "Green"))), IF(Status@row = "Complete", "Green", IF(OR(Status@row = "On Hold", Status@row = "Cancelled", Status@row = "Not Started"), "Gray", IF(OR(ISBLANK(Status@row), ISBLANK([Target End Date]@row)), "", IF(AND([Target End Date]@row <= TODAY(3), OR(Status@row <> "On Hold", Status@row <> "Cancelled", Status@row <> "Not Started", Status@row <> "Complete")), "Red", IF(AND([Target End Date]@row <= TODAY(7), OR(Status@row <> "On Hold", Status@row <> "Cancelled", Status@row <> "Not Started", Status@row <> "Complete")), "Yellow", "Green"))))))

Kelly

Kelly Moore

Hey @NCharleb

Looking at your screenshot, FINANCE is not a parent of any rows below it. As written above, the formula should work as you desire in the parent rows Treasury and Trust however it will result in Green in all other rows.

Indent the rows below Finance to create a Parent child relationships of Finance to all other rows. This will allow the roll up of the children to this top level.

If you have a formula for the non-parent rows, we can incorporate that into your roll-up formula. If you are manually entering the non-parent Health colors, then the formula above must manually be inserted into your parent rows unless we add a helper column for the manual entry and tweak your formula to account for this. The helper column approach is what I would do if I couldn't determine the color of the child row health color by a formula.

Indent the rows to make Finance a Parent. Try the formula above in the parent rows and let me know if it works, if it produces unexpected results, or if it produces an error (and what error). If we need to add more to the formula to differentiate the parent rows and the non-parent rows, let me know - we can do that.

kelly

NCharleb

Hi Kelly,

Thank you for taking the time to help.

I still need the child rows to roll up and as well the overall project.

So far I have this:

·        Project Health turns "Gray" for any row that has a Status of "On Hold" or "Canceled" or “Not Started”

·        Project Health turns "Green" for any row that has a Status of "Complete"

·        Project Health turns "Yellow" for any row that does not have a Status of "Complete", "On Hold", "Canceled" or “Not Started”, and the Target End Date is within the next 7 days

·        Project Health turns "Red" for any row that does not have a Status of "Complete", "On Hold", "Canceled" or “Not Started”, and the Target End Date is within the next 3 days or is in the past

·        Project Health is “blank” for any row where the Status and Target End Date cells are empty

=IF(OR(Status@row = "On Hold", Status@row = "Canceled", Status@row = "Not Started"), "Gray", IF(Status@row = "Complete", "Green", IF(AND(Status@row <> "", [Target End Date]@row <> ""), IF(Status@row = "Complete", "Green", IF([Target End Date]@row <= TODAY(3), "Red", IF([Target End Date]@row <= TODAY(7), "Yellow", "Green"))))))

Kelly Moore

Hey @NCharleb

I combined the Parent Row Rollup formula with the Non-Parent row formulas. The formula first looks for Parent rows by seeing if any children are present (Count of children If yes, it applies the parent formula. If false, it applies the non-parent formulas. See if this works for you

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 1, "Yellow", "Green"))), IF(Status@row = "Complete", "Green", IF(OR(Status@row = "On Hold", Status@row = "Cancelled", Status@row = "Not Started"), "Gray", IF(OR(ISBLANK(Status@row), ISBLANK([Target End Date]@row)), "", IF(AND([Target End Date]@row <= TODAY(3), OR(Status@row <> "On Hold", Status@row <> "Cancelled", Status@row <> "Not Started", Status@row <> "Complete")), "Red", IF(AND([Target End Date]@row <= TODAY(7), OR(Status@row <> "On Hold", Status@row <> "Cancelled", Status@row <> "Not Started", Status@row <> "Complete")), "Yellow", "Green"))))))

Kelly

NCharleb

It worked! Thank you so much Kelly!

NCharleb

Oh boy, symbols and rules have changed.

I am now looking at these rules:

Added - Project Health is "blank" for any row that has a Status of "On Hold" or "Cancelled" or “Not Started”

Project Health is “blank” for any row where the Status and Target End Date cells are empty

Changed colour - Project Health turns "BLUE" for any row that has a Status of "Complete"

Project Health turns "Yellow" for any row that does not have a Status of "Complete", "On Hold", "Cancelled" or “Not Started”, and the Target End Date is within the next 7 days

Project Health turns "Red" for any row that does not have a Status of "Complete", "On Hold", "Cancelled" or “Not Started”, and the Target End Date is in the past (this also had "is within the next 3 days" and when I remove it I get error #UNPARSEABLE)

Something not right. If you look at the parent line for Trust is shows "green". One child line complete and the other cancelled. I think it should be "blue" - yes?

This is the formula so far

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 1, "Yellow", "Green"))), IF(Status@row = "Complete", "Blue", IF(OR(Status@row = "On Hold", Status@row = "Cancelled", Status@row = "Not Started", Status@row = "On Hold"), "", IF(OR(ISBLANK(Status@row), ISBLANK([Target End Date]@row)), "", IF(AND([Target End Date]@row <= TODAY(3), OR(Status@row <> "On Hold", Status@row <> "Cancelled", Status@row <> "Not Started", Status@row <> "Complete")), "Red", IF(AND([Target End Date]@row <= TODAY(7), OR(Status@row <> "On Hold", Status@row <> "Cancelled", Status@row <> "Not Started", Status@row <> "Complete")), "Yellow", "Green"))))))