How to identify the latest record submitted via a form among all entries by the same submitter?
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!😊
Best Answer
-
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
-
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!
-
So so helpful, @Nick Korna! Thank you SO much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!