JOIN and COLLECT with multiple criteria and date - cross sheet reference
I have a sheet that uses a web form to gather data from multiple users across the fleet. The idea is for the managers to submit their site information each week on Tuesday. I have another sheet that is formatted vertically that will push out an automated alert to the executives at the end of business on Tuesday evening. The alert will summarize all entries submitted from the form for the current week only. I can't figure out why it isn't working, what am I missing??
(Sheet populated by webform)
(Sheet with formula and alert workflow to executives)
=JOIN(COLLECT({Safety, Security, Environmental}, {Plant / Site}, "New England Peaking", {Date}, "TODAY"))
Best Answer
-
Try this...
=JOIN(COLLECT({Safety, Security, Environmental}, {Plant / Site}, "New England Peaking", {Date}, TODAY()))
Answers
-
Try this...
=JOIN(COLLECT({Safety, Security, Environmental}, {Plant / Site}, "New England Peaking", {Date}, TODAY()))
-
It worked! Thank you so much!
-
Happy to help. 👍️
-
Next question - if a site has two entries submitted on the same day, is there a way to only include the most recent entry in the same workflow discussed above? The TODAY function would pull both entries and I would like to only pull the most recent entry.
-
Are new entries entered at the top or the bottom of the sheet?
-
At the bottom.
To explain the workflow a bit better, this is the thought process of what we are wanting to accomplish. The workflow should run every Tuesday, once a week.
- At 0800 EST you will receive an email request to complete the Executive Summary webform that we reviewed on last week’s training session. (If you don’t receive an email, let me know)
- Please fill out the form for your site as we discussed on last week’s training
- As soon as you submit the form a copy of your response will be sent to your GM and PM for review
- If you need to change your response, simply fill out a new webform. Smartsheet will use the latest revision for the Executive report.
- At 1900 EST the report will be sent to GM’s, PM’s and VPs for review.
-
I would suggest an auto-number column with no special formatting. Then a hidden helper column on the collection sheet with the following:
=MAX(COLLECT({Auto-Number}, {Plant / Site}, "New England Peaking", {Date}, TODAY()))
Then the JOIN/COLLECT would be tweaked like so:
=JOIN(COLLECT({Safety, Security, Environmental}, {Plant / Site}, "New England Peaking", {Date}, TODAY(), {Auto-Number}, @cell = [Hidden Helper]@row))
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives