Automating a Roll up for RYG Symbols for Health

NCharlebNCharleb
edited 11/25/21 in Formulas and Functions
11/25/21 Edited 11/25/21
Accepted

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.


Best Answer

Answers

  • Kelly MooreKelly 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

  • NCharlebNCharleb
    edited 11/26/21

    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([email protected] = "On Hold", [email protected] = "Canceled", [email protected] = "Not Started"), "Gray", IF([email protected] = "Complete", "Green", IF(AND([email protected] <> "", [Target End Date]@row <> ""), IF([email protected] = "Complete", "Green", IF([Target End Date]@row <= TODAY(3), "Red", IF([Target End Date]@row <= TODAY(7), "Yellow", "Green"))))))


  • Kelly MooreKelly Moore ✭✭✭✭✭
    Accepted Answer

    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([email protected] = "Complete", "Green", IF(OR([email protected] = "On Hold", [email protected] = "Cancelled", [email protected] = "Not Started"), "Gray", IF(OR(ISBLANK([email protected]), ISBLANK([Target End Date]@row)), "", IF(AND([Target End Date]@row <= TODAY(3), OR([email protected] <> "On Hold", [email protected] <> "Cancelled", [email protected] <> "Not Started", [email protected] <> "Complete")), "Red", IF(AND([Target End Date]@row <= TODAY(7), OR([email protected] <> "On Hold", [email protected] <> "Cancelled", [email protected] <> "Not Started", [email protected] <> "Complete")), "Yellow", "Green"))))))

    Kelly

  • NCharlebNCharleb
    edited 11/26/21

    It worked! Thank you so much Kelly!

  • NCharlebNCharleb
    edited 11/30/21

    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([email protected] = "Complete", "Blue", IF(OR([email protected] = "On Hold", [email protected] = "Cancelled", [email protected] = "Not Started", [email protected] = "On Hold"), "", IF(OR(ISBLANK([email protected]), ISBLANK([Target End Date]@row)), "", IF(AND([Target End Date]@row <= TODAY(3), OR([email protected] <> "On Hold", [email protected] <> "Cancelled", [email protected] <> "Not Started", [email protected] <> "Complete")), "Red", IF(AND([Target End Date]@row <= TODAY(7), OR([email protected] <> "On Hold", [email protected] <> "Cancelled", [email protected] <> "Not Started", [email protected] <> "Complete")), "Yellow", "Green"))))))


Sign In or Register to comment.