Formula Needed for Parent to Inherit Child Value

Lauren B

I don't think this is possible, but I need a formula where a parent row Status value reads "No Active Projects" until it has a child row below it with an "In Progress" status at which point the cell should just be blank. See image below.

I use the "No Active Projects" marker to determine whether or not that department title pushes to a dashboard report. Right now, we have to manually remember to change the value when the first child row turns to "In Progress", but it would be nice if a formula could adjust the value based on the child statuses. 

 

Andrée Starå

Hi Lauren,

It's possible. I'll get back to you with the formula soon.

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrée Starå

Try something like.

=IF(COUNTIF(CHILDREN(Status@row); "In Progress") > 0; ""; "No Active Projects")    

The same version but with the below changes for your and others convenience.  

=IF(COUNTIF(CHILDREN(Status@row), "In Progress") > 0, "", "No Active Projects")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting