Identify deliverable date at the parent level
I'm using these formulas to show the next milestone date:
Milestone checkbox column: =IF([Expected Finish*]@row = MIN(COLLECT([Expected Finish*]:[Expected Finish*], [Expected Finish*]:[Expected Finish*], >=TODAY())), 1, 0)
Next Milestone Date: =MIN(COLLECT([Expected Finish*]:[Expected Finish*], Milestone:Milestone, 1))
They work correctly to identify the next date regardless of parent/child level. I'd like to only pick up the dates at the "example deliverable" level (i.e., first child), not the task level (any grandchildren). Can I using something like an ANCESTOR() formula to accomplish this? I'm not sure where to put it in the formula(s).
Best Answer

Awesome, thanks @Genevieve P. ! I added the helper column (and intend to hide it as you suggested). I made a small modification to move the "Level:Level, 1" criteria later in the formula to get it to work:
Milestone checkbox column: =IF([Expected Finish*]@row = MIN(COLLECT([Expected Finish*]:[Expected Finish*], [Expected Finish*]:[Expected Finish*], Level:Level, 1)), 1, 0)
Next Milestone Date: =MIN(COLLECT([Expected Finish*]:[Expected Finish*], Milestone:Milestone, 1, Level:Level, 1))
Thanks so much!
Answers

You're exactly correct! You can use COUNT with ANCESTORS to identify row levels. If your row only has 1 Ancestor (one Parent), then it's what you're looking for.
To do this, I would suggest adding a helper column in your sheet (that you can hide, if needed). Simply add this to your helper column:
=COUNT(ANCESTORS(Deliverables@row))
Then make it a Column Formula.
Now you can use this helper column (I'll call it "level") as a criteria in your current formulas:
Milestone checkbox column: =IF([Expected Finish*]@row = MIN(COLLECT(Level:Level, 1, [Expected Finish*]:[Expected Finish*], [Expected Finish*]:[Expected Finish*], >=TODAY())), 1, 0)
Next Milestone Date: =MIN(COLLECT(Level:Level, 1, [Expected Finish*]:[Expected Finish*], Milestone:Milestone, 1))
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Awesome, thanks @Genevieve P. ! I added the helper column (and intend to hide it as you suggested). I made a small modification to move the "Level:Level, 1" criteria later in the formula to get it to work:
Milestone checkbox column: =IF([Expected Finish*]@row = MIN(COLLECT([Expected Finish*]:[Expected Finish*], [Expected Finish*]:[Expected Finish*], Level:Level, 1)), 1, 0)
Next Milestone Date: =MIN(COLLECT([Expected Finish*]:[Expected Finish*], Milestone:Milestone, 1, Level:Level, 1))
Thanks so much!

My apologies! You are absolutely correct, the range and criteria should be after the first reference in the Collect function. I should have had more coffee before responding. 🙂
I'm glad you were able to get it to work!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!