Find first blank in other column and return value in same column
At the top of the Milestones column, I would like SS to return the next task that does not have a date in the neighboring Date column. I've tried a few things, but cannot figure out the correct formula to do this.
Best Answer

Hey Laura,
This is pretty interesting. I was able to do this but I don't know how pretty it is. Here is the example sheet:
So here is what's happening,
 Helper Column added to identify first blank date cell in a given range of cells. You have to apply/customize this formula to the range of children cells you are applying it to.
 Formula in parent Milestone cell that looks at the helper column and collects the names of the Milestones if they are checked.
Here are the formulas from the sheet, but they make more sense in context,
=IF(COUNTIF(Date$2:Date@row, ISBLANK(@cell)) <> 1, 0, 1)
=JOIN(COLLECT(Milestone2:Milestone9, [Column2]2:[Column2]9, 1), ",")
Please let me know if that works for you!
Answers

Hey Laura,
This is pretty interesting. I was able to do this but I don't know how pretty it is. Here is the example sheet:
So here is what's happening,
 Helper Column added to identify first blank date cell in a given range of cells. You have to apply/customize this formula to the range of children cells you are applying it to.
 Formula in parent Milestone cell that looks at the helper column and collects the names of the Milestones if they are checked.
Here are the formulas from the sheet, but they make more sense in context,
=IF(COUNTIF(Date$2:Date@row, ISBLANK(@cell)) <> 1, 0, 1)
=JOIN(COLLECT(Milestone2:Milestone9, [Column2]2:[Column2]9, 1), ",")
Please let me know if that works for you!

That works great. Thank you so much!

Hello,
I tried this but it is pulling the value for both blank columns. Is there a formula for the helper column to check mark the first blank cell?

Hey @IMC
Without knowing more about your scenario, I would suggest changing the JOIN collect to be an INDEX collect instead:
=INDEX(COLLECT(Milestone2:Milestone9, [Column2]2:[Column2]9, 1), 1)
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.2K Get Help
 358 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 135 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!