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.
Answers
-
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
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!