Pull Most Recent Entry for Given Item in Another Sheet

CMI

Hello,

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

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.

Kelly