Most Recent Date

edited 12/09/19 in Formulas and Functions

Hi everyone... I've been struggling with how to do the right formula here and would love if anyone could help.  I have a form which people are to fill out twice a month to do a temperature check on their progress.  The data will have the date, name, etc., then their "mood" (a dropdown list), and each entry will be a separate line which we also intend to use for trending.

With the end goal of putting this into a chart on a dashboard, I am trying to create a formula that will then give me their most recent "mood".  From what I have found, MAX(COLLECT) will be my best bet, but I'm not sure the syntax as I keep getting stuck at how to get the date info  I'm almost embarrassed to put my attempt at it, but here we go:

=INDEX({Data Range 2}, 1, 11, (MAX(COLLECT({Mood}, {Date}, ???}))))

Any help would be appreciated!  I'm stuck, and the only one in my office who creates in Smartsheet.  Thank you in advance!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!