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()))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Try this...
=JOIN(COLLECT({Safety, Security, Environmental}, {Plant / Site}, "New England Peaking", {Date}, TODAY()))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It worked! Thank you so much!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives