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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi Genevieve
Perfect thank you - Child column is now hidden
Thank you
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!