How to identify the latest record submitted via a form among all entries by the same submitter?

Options

My smartsheet dataset is comprised of form submissions. Businesses are required to submit the form monthly, and I need to assign a "red" color to businesses who have not submitted a form in the last 60 days, and "yellow" to businesses who have not submitted a form in the last 30 days. I do have a separate sheet listing all the names of businesses who should be submitting the monthly form. If I could have help on how to go about these color assignments, I'd appreciate it immensely.

Thank you!😊

Tags:

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    If your dataset form captures the business submitting the form entry, you can use a MAX & COLLECT (along with some cross sheet references) in your summary sheet with the list of business names along the lines of:

    =MAX(COLLECT({Form submission date},{Business submitting},[Business Name]@row))

    You can then use conditional formatting to colour the rows, though this is probably easiest with the addition of a column calculating days since last submission:

    =TODAY()-[Latest Form]@row

    Your conditional formatting can then look something along these lines:

    To get something like this:

    Hope this helps, but if you've any problems/questions then just let us know!

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    If your dataset form captures the business submitting the form entry, you can use a MAX & COLLECT (along with some cross sheet references) in your summary sheet with the list of business names along the lines of:

    =MAX(COLLECT({Form submission date},{Business submitting},[Business Name]@row))

    You can then use conditional formatting to colour the rows, though this is probably easiest with the addition of a column calculating days since last submission:

    =TODAY()-[Latest Form]@row

    Your conditional formatting can then look something along these lines:

    To get something like this:

    Hope this helps, but if you've any problems/questions then just let us know!

  • Barbara Silva
    Options

    So so helpful, @Nick Korna! Thank you SO much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!