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

Barbara Silva

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!😊

Nick Korna

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!

Nick Korna

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

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