RYGB - need to update Parent row with status of children

Peggy Parchert

I am trying to get the Status in the Parent row to update automatically based off the Status of the child rows. I have the following formula in the Status column of the child rows:

=IF([Phase Status]@row = "complete", "Blue", IF([Phase Status]@row = "At Risk", "Red", IF([Phase Status]@row = "On Hold", "Yellow", IF([End Date]@row < TODAY(), IF([Phase Status]@row = "N/A", " ", "Red"), IF([Phase Status]@row = "In Progress", "Green", "")))))

That is working great!

But I can't get the Parent row to reflect the Status automatically. It should be show:

Red if any of the child rows are Red;

Yellow if any of the child rows are Yellow; 

Green if any of the child rows are Green;

Blue if all child rows are Blue;

I've attached a screenshot of my sheet. Any assistance would be greatly appreciated.

L_123

in the status column in the parent cell

=if(match("red",children(),0)>0,"Red",if(match("Yellow",children(),0)>0,"Yellow",if(match("Green",children(),0)>0,"Green", if(match("Blue",children(),0)>0,"Blue"

I don't fully understand why green is higher on the hierarchy than blue, but you can rearrange the hierarchy by moving the if statements around. 

@emipathy

I believe you could do a formula as follows: 



=IF(COUNT(CHILDREN(), "Red")>0, "Red", IF(COUNT(CHILDREN(), "Yellow")>0, "Yellow", IF(COUNT(CHILDREN(), "Green")>0, "Green", "Blue")))

That would give you blue if none of the other conditions are met... Which should be that they're all blue. I'm sure there's a better way to put in "if all children "blue", "blue"", but I'm not certain on how to do it and would rather not mislead. 

Paul Newcome

You would need to change these COUNT functions to COUNTIFS functions for this to work. Otherwise this formula will work just as well as the above MATCH function.

Paul Newcome

Another option...

 

=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(CONTAINS("Green", CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN(Project@row)), "Blue"))))

Peggy Parchert

I failed to provide a screenshot of the Legend for this. Since SmartSheet only has RYGB as options. So if the Phase Status is Not Started or N/A the Status will be blank. That is the another problem.

Paul Newcome

Try something similar to this...

 

=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(CONTAINS("Green", CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN(Project@row)), "Blue", IF(COUNTIFS(CHILDREN(), ISBLANK(@cell)) = COUNT(CHILDREN(Project@row)), "")))))

.

It will work in the order each IF statement is in.

 

If there is at least one Red, Red

If there is at least one Yellow and no Reds, Yellow

If there is at least one Green and no Yellows or Reds, Green

If all are Blue, Blue

If all are Blank, Blank

 

Peggy Parchert

Thank you very much! Greatly appreciate the assistance.

Paul Newcome

Happy to help! 

Andrée Starå

There are so many options/workarounds! 

 

Paul Newcome

Haha. That about sums it up. Or adds everything together. Or combines it all into one. Or something about joins and collects. Did I miss anything? Hahaha

Andrée Starå

Looks like you got them all but if I find something I'll add it. 

L_123

=if(find("Red",join(children(),",")),"Red",

That could be another option

Paul Newcome

=if(find("Red",join(children(),",")) > 0,"Red",

 

L_123

Got me there hah. typed to fast

Paul Newcome

Hahaha. I do that A LOT. The backspace and delete keys get used quite frequently on my keyboards.