Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Pulling Cells Information by Date into Sheet Summary
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!
Best Answer
-
You would use a JOIN/COLLECT.
=JOIN(COLLECT(Location:Loaction, [Live Date]:[Live Date], AND(@cell>= TODAY(), @cell<= TODAY(14))), CHAR(10))
Answers
-
Would something like this help?
=IF(AND([Live Date]@row >= TODAY(), [Live Date]@row <= TODAY(14)), 1, 0)
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
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
-
You would use a JOIN/COLLECT.
=JOIN(COLLECT(Location:Loaction, [Live Date]:[Live Date], AND(@cell>= TODAY(), @cell<= TODAY(14))), CHAR(10))
-
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?
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
