RYGB - need to update Parent row with status of children

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.

Screen Shot 2019-09-17 at 9.38.49 AM.png

Find more posts tagged with

Education

Comments

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...

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!

mindblown

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.

searching

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Project Comment/ Update
Hi, Is there a feature in Smartsheet that allows us to add comments daily, weekly, or monthly, and filter them later to review what happened on the project during a specific week or month? or create report ? Thanks JCG
Advise on Workgroups and access for Groups
I have Workspaces that have sheets containing sensitive information. The Workspaces have Individual access to a few people. With in the Workspace, I created Dashboards, with Dynamic Views. Each is set with specific Groups to access specific information. The users in the groups can not see or access the Dashboards, unless I…
Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…