Pull Most Recent Entry for Given Item in Another Sheet

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.

Find more posts tagged with

Accepted answers

All comments

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

Bradley CO

Kelly,

I have been struggling with this issue as well. I tried using your first equation on my sheet A, however I am getting an Invalid Reference argument. I made sure all of my columns were spelled correctly and I made sure the column was in the date format. I then tried switching up the curly braces {} for brackets [] (since I see that when referencing column names) and now I am getting a syntax error. Any idea of what I might be doing wrong with that first equation.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.