Find first blank in other column and return value in same column

LauraD

Construction Projects.pdf

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.

M Underbrink

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:

https://publish.smartsheet.com/3b262e7c57ca435e9f91e255dd09eef4

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!

M Underbrink

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:

https://publish.smartsheet.com/3b262e7c57ca435e9f91e255dd09eef4

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!

LauraD

That works great. Thank you so much!

IMC

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?

Genevieve P.

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