Add child rows that meet certain criteria and ALL their parents in a report

isma

Hello, I want to do the following:

I have a plan built with several levels of granparent, parent, child rows

I want to add in a report all rows that have Status: Complete and ALL their parent and grandparents for contextual information. The report should look like this

Task Name Status

Grandparent

Parent

Child 1 Complete

Child 3 Complete

I'm using the following formula but its not working:

=IF(COUNTIFS(ANCESTORS([Task Name]@row), (Status@row = "Complete")) > 0, 1)

1) Can I do this type of filtering from the report sheet filter capabilities or do I need to create a helper column in the actual sheet I'm getting the data from?

2)Which formula would you recommend to checkbox both child rows that meet criteria and ALL their ancestor rows (even if they dont meet criteria)?

Thank you

Paul Newcome

Try this:

=IF(OR(COUNTIFS(DESCENDANTS(Status@row), @cell = "Complete")> 0, Status@row = "Complete"), 1)

Joseph Adams

https://community.smartsheet.com/discussion/109820/using-an-if-formula-for-parents-children-rows

This article may be of some help

isma

Hello @Joseph Adams

Can you explain the meaning of this formula please: I need to flag basically each row where status is "Complete" and all the parents and grandparents of that row.

Paul Newcome

Try this:

=IF(OR(COUNTIFS(DESCENDANTS(Status@row), @cell = "Complete")> 0, Status@row = "Complete"), 1)

isma

Hello @Paul Newcome, thank you this worked perfectly. If I want to add one more condition (Status=Complete + End Date=Last 7 days) so child rows complying with this criteria (meaning, completed task in last 7 days) get flagged and ALL their parents and grand parents. What would you suggest?

Regards

Paul Newcome

You would add another range/criteria set to the COUNTIFS as well as an AND function within the OR to group the Status@row and [End Date]@row.

=IF(OR(COUNTIFS(DESCENDANTS(Status@row), @cell = "Complete", DESCENDANTS([End Date]@row), @cell>= TODAY(-7))> 0, AND(Status@row = "Complete", [End Date]@row>= TODAY(-7))), 1)