Parent Row Status Formula

hbeckner

I need a formula to update the status on the parent row based on status values in the child rows below. 

Status Options

Not Started

In Progress

For Approval - Internal

For Approval - External

For Approval - Executive

Revisions In Progress

Complete / Approved

Complete / Killed

On Hold

Backlog

Logic I want to formula to include:

If all children have a status of "Not Started", parent has a status of "Not Started"

If one (or more) child has a status of "In Progress", parent has a status of "In Progress"

If one (or more) child has a status of "For Approval - Internal", parent has a status of "For Approval - Internal"

If one (or more) child has a status of "For Approval - External", parent has a status of "For Approval - External"

If one (or more) child has a status of "For Approval - Executive", parent has a status of "For Approval - Executive"

If one (or more) child has a status of "Revisions In Progress", parent has a status of "Revisions In Progress"

If all children have a status of "Complete / Approved", parent has a status of "Complete / Approved"

If all children have a status of "Complete / Killed", parent has a status of "Complete / Killed"

If all children have a status of "On Hold", parent has a status of "On Hold"

If all children have a status of "Backlog", parent has a status of "Backlog"

Not sure if this is possible but would really like to include the following as well:

If child has a status of "Complete / Approved" and the "Name" column of that child row contains "Design Proofing / Review", parent has status of "Complete / Approved"

Debbie Sawyer

@hbeckner

Yes, this is done by nesting COUNTIF statements inside an IF.

I have exploded the first few statements for you. Just carry on in this fashion for the rest of the clauses.

=IF(COUNTIF(CHILDREN(),"Not Started")=COUNT(CHILDREN()),"Not Started",IF(COUNTIF(CHILDREN(),"In Progress")>0,"In Progress",IF(COUNTIF(CHILDREN(),"For Approval - Internal")>0,"For Approval - Internal")))

Bear in mind that for each IF statement, there needs to be a ")" at the end of the formula, so here I have shown 3 IF clauses, therefore have 3 ))) at the end. You will end up with lots more! Don't forget to close the COUNTIF ) at the end of each COUNTIF set of arguments. In this type of nested formula, just take it slowly and watch the placement of brackets and commas!

The Order of statements is also important, as if an IF statement returns true then no other statements are read. So if more than one of these can be true (i.e. the if there is 1 or more child, then set the parent to xx) the order of these needs to be correct to set the precedent properly.

Happy to connect and show this on zoom, if you need further help.

Good luck

Kind regards

Debbie

debbie.sawyer@smarterbusinessprocesses.com

hbeckner

How do I add this:

If child has a status of "Complete / Approved" and the "Name" column of that child row contains "Design Proofing / Review", parent has status of "Complete / Approved"

Debbie Sawyer

Hello @hbeckner

Try this...

=IF(COUNTIFS(CHILDREN(),"Complete / Approved",CHILDREN(Name@row),"Design Proofing / Review")>0,"Complete / Approved")