Join Collect from Other Sheet only IF not blank

I'm looking to collect all project updates in a single cell with a running history. The information lives in separate lines in a sheet:

Current Formula:

=JOIN(COLLECT({Timestamp + Note}, {RowID}, [Project ID]@row), CHAR(10) + " || ")


Update needed: "SKIP" rows where the Timestamp + note is blank.

I tried adding a criterion for not(isblank) but was unable to do it successfully.
What do I need to add to this?

=JOIN(COLLECT({Timestamp + Note}, {RowID}, [Project ID]@row), CHAR(10) + " || ")

Answers

  • Jennifer Kurtz
    Jennifer Kurtz ✭✭✭✭

    Hi! to add the criterion for skipping blank cells, you can reference that criterion range ({Timestamp + Note}, I assume), and use the criteria <>"" — in other words, something other than blank.

    =JOIN(COLLECT({Timestamp + Note}, {RowID}, [Project ID]@row), {Timestamp + Note}, <>""

    (I'm guessing there's a way to use the Not(isblank) here instead, but honestly i get confused about the syntax when combining that in with other things! :>)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!