Join Collect with Multiple Criteria

Paula Reynolds

I have been searching the community posts for a solution, but have not been able to find one that works. I have a sheet that I need to combine multiple columns into one helper column. Here is the description of what I am trying to do (screenshot included below for reference)

If a date is less than 30 days from today, add the task name and the date to the helper column. The current formula I am using is: =JOIN(COLLECT(Task@row, June@row, <TODAY(30)))

However, it is only adding the task name to the helper column, not the date as well.

I actually need a formula that will look across the columns July through June and add the task and date if it is less than 30 days out.

jmyzk_cloudsmart_jp

Hi @Paula Reynolds

If you are willing to add another helper column, you can do it this way.😀

Helper Column 1

=JOIN(COLLECT(July@row:June@row, July@row:June@row, ISDATE(@cell), July@row:June@row, ABS(@cell - TODAY()) < 30))

Helper Column 2

=IF(ISTEXT([Helper Column 1]@row), Task@row + " " + [Helper Column 1]@row)

https://app.smartsheet.com/b/publish?EQBCT=15a2ac5d13384368b779349d51609720