Exclude Rollup / Summary Tasks from Summary Sheet
Hi All,
My Smartsheet has Rollup / Summary Tasks, Deliverables/Tasks and Milestones. On the Summary Sheet, I only want to count the actual Deliverables/Tasks i.e. not the Rollup/Summary tasks or milestones.
I found how to add a column that determines whether a row is a child or a parent using -
=IF(COUNT(CHILDREN([Task Name]@row)) = 0, 1)
And I've managed to add a formula to my Summary sheet that reads -
=COUNTIFS(Workstream:Workstream, "Supplier Engagement", Status:Status, "Yet to Start", Child:Child, 1, Duration:Duration, "<>0")
However, I dont really want the Child column in my sheets and would prefer to have the formula in the Summary sheet (so replacing 'Child:Child, 1')
Any guidance on how to combine these?
Thank you
Best Answers
-
Hi @GTN
You've done exactly what I would do! Hierarchy is specific to each sheet, so adding a helper column to identify the hierarchy and then using that helper column in your cross-sheet formula is the solution for this type of formula.
If you don't want the helper Child column showing, you can Hide it in the source sheet but it will still work fine in the formula.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi Genevieve
Perfect thank you - Child column is now hidden
Thank you
Answers
-
Hi @GTN
You've done exactly what I would do! Hierarchy is specific to each sheet, so adding a helper column to identify the hierarchy and then using that helper column in your cross-sheet formula is the solution for this type of formula.
If you don't want the helper Child column showing, you can Hide it in the source sheet but it will still work fine in the formula.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi Genevieve
Perfect thank you - Child column is now hidden
Thank you
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!