Pulling Cells Information by Date into Sheet Summary

Karla.Fairbairn

Hi All,

I'm pretty new at Smartsheet and I'm getting the hang of it, but I'm stuck on this one. What formula can I use to pull in any Location name that has a "Live Date" within 2 weeks of today's date?

Here's my table:

Any help would be greatly appreciated!

Paul Newcome

You would use a JOIN/COLLECT.

=JOIN(COLLECT(Location:Loaction, [Live Date]:[Live Date], AND(@cell>= TODAY(), @cell<= TODAY(14))), CHAR(10))

Jason Albrecht

Hi @Karla.Fairbairn

Would something like this help?

=IF(AND([Live Date]@row >= TODAY(), [Live Date]@row <= TODAY(14)), 1, 0)

Karla.Fairbairn

Hi @Jason Albrecht !

Yes, that setup would work within the sheet, but I forgot to mention that I'm trying to pull the location name(s) into my sheet summary. Is that even possible?

Thanks,

Karla

Paul Newcome

You would use a JOIN/COLLECT.

=JOIN(COLLECT(Location:Loaction, [Live Date]:[Live Date], AND(@cell>= TODAY(), @cell<= TODAY(14))), CHAR(10))

Karla.Fairbairn

Thank you, @Paul Newcome ! That worked beautifully.

Now, what if I want to combine =JOIN(COLLECT(Location:Location, [Live Date]:[Live Date], AND(@cell >= TODAY(), @cell <= TODAY(14))), CHAR(10))

and

=JOIN(COLLECT([Live Date]:[Live Date], [Live Date]:[Live Date], AND(@cell >= TODAY(), @cell <= TODAY(14))), CHAR(10))

so that I'm pulling the locations and corresponding live dates for anything in the next 14 days? Is it possible?

Paul Newcome

You would need to insert a helper column that combines the location and date on each row and then use the JOIN/COLLECT on this new column.