Parent cell - Status symbol total

MYRIAMMC

Hi there,

How can the parent cell (circled in blue) automatically reflect the correct color/symbol concerning the tasks below? When I say automatically, I refer to do it by default, not me selecting the symbol all the time. Can this be done with a formula, and if so, which formula?

Thanks in advance

M

Ryan Nix

Yes, you can set this to be automatic though you'll need to set up a formula to tell it when to change automatically. Below is a formula I use on the parent row:

=IF(COUNTIFS(CHILDREN(), "Red") > 6, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 1, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 3, "Yellow", "Green")))

And the Children rows I use the following formula:

=IF([Start Date]25 > TODAY(), "Gray", IF(AND(Status25 < 1, [End Date]25 < TODAY()), "Red", "Green"))

To start, I have the children row flag items as gray if the start date is after today. If it isn't and the status is less than 100% AND if today is past the end date, then I want it to be red. If the status is under 100% and today is before the end date, then I want it to be green.

Now the parent row will check the children rows. If there are more than 6 red colors, then I want the parent row to be red. Otherwise, if there is more than 1 red, I want it to be yellow or if there is more than 3 yellows, I want it to be yellow as well. If it doesn't meet any of those criterias, then I want it green.

Hopefully that makes sense for you haha - it's a bit late and I'm exhausted.