JOIN and COLLECT with multiple criteria and date - cross sheet reference

Ashley Admire

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"))

Paul Newcome

Try this...

=JOIN(COLLECT({Safety, Security, Environmental}, {Plant / Site}, "New England Peaking", {Date}, TODAY()))

Paul Newcome

Try this...

=JOIN(COLLECT({Safety, Security, Environmental}, {Plant / Site}, "New England Peaking", {Date}, TODAY()))

Ashley Admire

It worked! Thank you so much!

Paul Newcome

Happy to help. 👍️

Ashley Admire

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.

Paul Newcome

Are new entries entered at the top or the bottom of the sheet?

Ashley Admire

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.

1. 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)
2. Please fill out the form for your site as we discussed on last week’s training
3. As soon as you submit the form a copy of your response will be sent to your GM and PM for review
4. If you need to change your response, simply fill out a new webform. Smartsheet will use the latest revision for the Executive report.
5. At 1900 EST the report will be sent to GM’s, PM’s and VPs for review. 

Paul Newcome

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))