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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • It worked! Thank you so much!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com