most recent instance of a dropdown option

Bryan Walsh

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!

Paul Newcome

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 Newcome

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)

Bryan Walsh

Paul,

You're a rock star, thank you!

Paul Newcome

Happy to help! 👍️