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

Marcos Aguilar

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

Paul McGuinness

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