Check checkbox column for both the parent and child row if the child row contains "Editing"
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))
Best 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"
Answers
-
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"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!