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
Help Article Resources
Categories
Check out the Formula Handbook template!
