Formula Needed for Parent to Inherit Child Value

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.

SS Status Formula Example.PNG

Find more posts tagged with

Sheets

Formulas

Reports

Legal

IT & Operations

Project Management

Enhancement Request

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.