Question

I want to generate a summary report of all items that need editing. However the "editing" line item only says "Editing", so I want to also bring in the parent row that says the name of the document being edited. I have a column that I want to be checked for any child row that says "editing" and also the associated parent row of the child row that has the word "editing".

This is what I have tried but it's not quite right:

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIFS(DESCENDANTS(FIND("Tech Editing", [Task Name]@row)>=1, 1, 0), IF(AND(FIND("Tech Editing", [Task Name]@row) >= 1, 1, 0))

KPH · Accepted Answer

I can't unravel the formula so I'm just looking at the words. Would something like this work?

1 Check checkbox column if the current row contains "Editing"

=IF(CONTAINS("Tech Editing", [Task Name]@row), 1)

If the cell in Task Name contains "Tech Editing" the formula will return 1. In a checkbox column, 1 will check the box.

2 Check checkbox column in parent row if child row contains "Editing"

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN([Task Name]@row), CONTAINS("Tech Editing", @cell)) > 0, 1))

If the count of children is more than 0 this is a parent row. If that is true, if the count of children containing "Tech Editing" is greater than 0, return 1.

3 Combine formula 1 an 2

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN([Task Name]@row), CONTAINS("Tech Editing", @cell)) > 0, 1), IF(CONTAINS("Tech Editing", [Task Name]@row), 1))

Add the formula in part 1 to the position that is evaluated if the row is not a parent. So if not a parent row, the box is ticked if the row itself contains "Tech Editing"