most recent instance of a dropdown option
Hello,
I'm new to Smartsheet, but I've been learning! I'm building out an inventory system and am having a little trouble conceptualizing a needed aspect. I have devices organized into kits. I've made a new sheet with a form to track whenever an employee physically verifies the contents of a kit. To simplify, let's say column 1 is a dropdown with kit options (Kit Alpha, Kit Bravo, Kit Charlie, etc), column 2 is a date column, and column 3 is the contact of whomever did the inspection (this data is collected via a form).
I would like to show on a dashboard or report a list of the most recent date of inspection along with who completed the inspection for EACH kit.
Any help would be appreciated, tia!
Best Answer
-
You would set up a sheet like so...
Kit..........Most Recent Date.............Most Recent Person
Alpha...................f1.........................................f2
f1 would be:
=MAX(COLLECT({Other Sheet Date Column}, {Other Sheet Kit Column}, Kit@row)
and f2 would be...
=INDEX(COLLECT({Other Sheet Contact Column}, {Other Sheet Date Column}, [Most Recent Date]@row, {Other Sheet Kit Column}, Kit@row), 1)
Answers
-
You would set up a sheet like so...
Kit..........Most Recent Date.............Most Recent Person
Alpha...................f1.........................................f2
f1 would be:
=MAX(COLLECT({Other Sheet Date Column}, {Other Sheet Kit Column}, Kit@row)
and f2 would be...
=INDEX(COLLECT({Other Sheet Contact Column}, {Other Sheet Date Column}, [Most Recent Date]@row, {Other Sheet Kit Column}, Kit@row), 1)
-
Paul,
You're a rock star, thank you!
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!