How to update the ancestor/parent rows with symbol colors that reflect child rows using a formula?

Hi

I would like to have the parent row have a formula that feed up from the child rows (the child rows have their own formula that changes status color depending on the date). so for example:

GREEN = In Progress

YELLOW = Attention

RED = At Risk

BLUE = Completed

-The Overall Status (parent row) is GREEN if all subcategories (child row) are green or GREEN with BLUE, but if 1 or more subcategories turn to YELLOW turn the overall status to YELLOW, but if 1 or more subcategories turn to RED turn the overall status to RED, but if all the subcategories turn to blue turn the overall status to BLUE

And then I would like to have the ancestor row have a formula that feed up from the parent rows, following the same rules as the previous ones.


Thank you in advance

Best Answer

  • Paul McGuinness
    Paul McGuinness Overachievers
    Answer ✓

    Hi @Marcos Aguilar

    This formula should do what you need:

    =IF(CONTAINS("RED", CHILDREN()) = true, "Red", IF(CONTAINS("YELLOW", CHILDREN()) = true, "Yellow", IF(CONTAINS("GREEN", CHILDREN()) = true, "Green", "Blue")))

    Tested and working, formula should be applied to the parent and ancestor rows in the Overall status column.

    Hope it helps

    Thanks

    Paul

Answers

  • Paul McGuinness
    Paul McGuinness Overachievers
    Answer ✓

    Hi @Marcos Aguilar

    This formula should do what you need:

    =IF(CONTAINS("RED", CHILDREN()) = true, "Red", IF(CONTAINS("YELLOW", CHILDREN()) = true, "Yellow", IF(CONTAINS("GREEN", CHILDREN()) = true, "Green", "Blue")))

    Tested and working, formula should be applied to the parent and ancestor rows in the Overall status column.

    Hope it helps

    Thanks

    Paul

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!