Formula to get most recent status for a particular client

David Hilliard

This is a repost of a question I ended up solving myself, but have run into another issue. Here's the setup:

I have a "History" sheet that logs the results of form submissions:

I have a second sheet, the "Summary" that should look something like the below image, showing the status of each category from the most recent survey form:

You'll notice that the status summary is incorrect for Client 2 and Client 3, because my formula is grabbing the max date, and when forms are submitted on the same day, this will have duplicates. Here's the formula:

This formula worked great when the "Date Created" in the history column was an auto-numbered "Created" field, because the entries had timestamps, but it's been requested that the forms allow for manual date entry.

In addition to trying to make this work again with a manual date, I'd also like to present the SECOND most recent survey result. So I'd have "Current Client Satisfaction" and the next column would be "Previous Client Satisfaction". I think I can do that with LARGE(), but the formula in it's current incarnation needs a fix before I can move on to this problem.

Thanks!

Paul Newcome

You are going to want to insert a column on the destination sheet where you house the MAX/COLLECT for the date. Then you would adjust your INDEX/MATCH to an INDEX/COLLECT.

=INDEX(COLLECT({Satisfaction Column}, {Date Column}, [Date Helper Column]@row, {Client Column}, [Client Column]@row), 1)

The reason for breaking it apart like this is because you cannot nest a COLLECT inside of a COLLECT.

Paul Newcome

You are going to want to insert a column on the destination sheet where you house the MAX/COLLECT for the date. Then you would adjust your INDEX/MATCH to an INDEX/COLLECT.

=INDEX(COLLECT({Satisfaction Column}, {Date Column}, [Date Helper Column]@row, {Client Column}, [Client Column]@row), 1)

The reason for breaking it apart like this is because you cannot nest a COLLECT inside of a COLLECT.

David Hilliard

Worked out great, and using a second helper column with LARGE instead of MAX got me where I needed for the second most recent results. Thanks a ton! You've been solving problems here for years, man!

Paul Newcome

Happy to help. 👍️

And yes. I've been fumbling through here for about 3 years now. The learning tracks and help articles that Smartsheet provides can be great for learning the basics, but I found that regular visits here to the Community presented a lot more opportunity to really learn how to use Smartsheet as it provides real world use cases alongside collaborative problem solving and the possibility of many different solution approaches for the same problem.