Pull Most Recent Entry for Given Item in Another Sheet


I am attempting to create a sheet that uses form fill information from a separate collection sheet to update the status of equipment. This requires that only the most recent entry using specific criteria is utilized.

Here is an example of what I am trying to solve for. Sheet A is for capturing data from a form. The form will capture data on equipment that sees regular service. Sheet A will see recurring entries for the equipment.

For example. On 12.30.22, Equipment 1 saw a service. At that time Equipment 1 had a light changed, and the equipment hours were logged - 1010 hours. On 1.1.23, Equipment 1 saw another service, where a tire was changed, and the equipment hours were then logged - now 1018 hours. I would like Sheet B to capture the hours of Equipment 1 based on the most recent entry in Sheet A.

Thank you in advance.


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @CMI

    A two step process of first a MAX/COLLECT followed with an INDEX/COLLECT should give you what you want- assuming your sheet B has the equipment names or IDs in a list.

    Notes: (a) The formulas below refer to a column I called 'Equipment Name'. You will need to edit the formulas to insert the actual name of your Sheet B column.

    (b) Remember you will have to manually create your cross-sheet references using the Formula window. You cannot simply copy paste these formulas into your sheet. (c) Also, the ISDATE() term is included to help prevent any errors from non-date cells in Sheet A.

    Step1: Find the Last Date Entry pertaining to a specific piece of equipment. This formula will go into a Date formatted column on Sheet B. You can call it whatever you like but I'll refer to it as [Latest Date]

    =MAX(COLLECT({Sheet A Date}, {Sheet A- Date column}, ISDATE(@cell), {Sheet A- Equipment Name column}, [Equipment Name]@row))

    Step2: Collect the Hours using the [Recent Date] and [Equipment Name] to match the info in Sheet A

    =INDEX(COLLECT({Sheet A- Hours column), {Sheet A- Date column}, ISDATE(@cell), {Sheet A- Date column}, [Latest Date]@row, {Sheet A- Equipment Name column}, [Equipment Name]@row),1)

    Will these work for you? Let me know if you have any questions.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!