Find last Combination and Return Date

Simon Wilcock
Simon Wilcock ✭✭✭✭
edited 02/19/24 in Formulas and Functions


I want to be able to find a previous combination of an entry and return the date for that entry (not the created date, but the date in the date field)

I've been using the following to find the highest previous date but I want to integrate an extra criteria which is the last time a certain combination appeared.

=MAX(COLLECT(Date:Date, Date:Date, @cell < Date@row))

For example, when I add the last row for Manchester and Audit 1, I want the next cell to tell me when the last audit was completed, so in this case 05/07/2023. Ideally, also if there is no previous audit, enter None instead.

I've added a helper field to combine the Location and Type field into a single cell and then use that Helper column in the criteria but its not working.



  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 02/16/24

    Hey @Simon Wilcock

    Try this. If the system autonumber column isn't already in your sheet, you'll need that (Or, you can use the Created Date in it's place if you already have that. You would substitute [Row ID] with Created, in the formula below

    =IFERROR(INDEX(Date:Date, MATCH(MAX(COLLECT(Date:Date, Date:Date, ISDATE(@cell), Location:Location, Location@row, Type:Type, Type@row, [Row ID]:[Row ID], @cell < [Row ID]@row)), Date:Date, 0)), "None")

    Will this work for you?


  • Simon Wilcock
    Simon Wilcock ✭✭✭✭

    Great, this is good for me. Thanks Kelly 😀

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!