Automating a Roll up for RYG Symbols for Health

NCharleb
NCharleb ✭✭
edited 11/25/21 in Formulas and Functions

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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(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

Answers

  • Kelly Moore
    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
    NCharleb ✭✭
    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(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
    Kelly Moore ✭✭✭✭✭✭
    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(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
    NCharleb ✭✭
    edited 11/26/21

    It worked! Thank you so much Kelly!

  • NCharleb
    NCharleb ✭✭
    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(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"))))))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!