Pull Last Entry from One Sheet to Another Based on Criteria
I'm trying to figure out a formula to add the last or latest "Status Notes" entry from our Work Request Monthly Update sheet to the correct/matching Work Request's "Status Notes" on our Master Work Log Sheet.
Both sheet's Primary Column's are named Work Request Number and have mirroring numbers. The Monthly Update sheet receives updates monthly, so I'd like the last "Status Notes" for a specific Work Request Number to be copied into the "Status Notes" column on the Master Work Log Sheet.
Any thoughts?
Best Answer
-
To match on two criteria, you would actually need to use an INDEX/COLLECT which would in turn require a helper column with an additional formula since you can't nest a COLLECT function inside of another COLLECT function.
But...
The way the MATCH function works when evaluating a single column is from top to bottom. Once it finds the first match, it stops looking and returns that row number. If you have new forms being populated a the top, then the most recent will be the one picked up by the MATCH function.
Then we can stick with the INDEX/MATCH and only have to match on the work request number.
=INDEX({Range to pull from}, MATC([Work Request Number]@row, {Other Sheet Work Request Number}, 0))
Answers
-
How is the Monthly Update Sheet populated?
-
@Paul Newcome it is populated by the chairs submitting a monthly update by Smartsheet form
-
@Paul Newcome the funny things is I think I did this a few months ago with something I was going to do but didn't, went back in the activity history and found this formula:
=INDEX({Weekly Report - Servicing Transfers (Phase Range 1}, MATCH(MAX({Weekly Report - Servicing Transfers (Phase Range 2}), {Weekly Report - Servicing Transfers (Phase Range 2}))
but not I have no idea which column was which and can't get it to work for what I'm trying to do now (face palm).
-
The first range is going to be the range you want to pull from. If you are still unsure, I'd be happy to help you rebuild if you don't mind sharing a little more detail.
-
@Paul Newcome ok figured out my last formula, but now would still need to added in the matched Work Request Number, because using the formula:
=INDEX({Work Request Monthly Update Repository Range 3}, MATCH(MAX({Work Request Monthly Update Repository Range 1}), {Work Request Monthly Update Repository Range 1}))
returns nothing because (I think) I need something that matches the work request numbers?
-
Let's first start with using a zero in the third portion of the MATCH function and see if that helps.
=INDEX({Work Request Monthly Update Repository Range 3}, MATCH(MAX({Work Request Monthly Update Repository Range 1}), {Work Request Monthly Update Repository Range 1}, 0))
-
@Paul Newcome here is a screenshot:
I need the last Status Notes from the right side sheet to populate in the left side of the sheet based on matching Work Request number. It should change each month. Does that make sense?
-
Is the form populating at the top or the bottom of the sheet?
-
@Paul Newcome bottom
-
@Paul Newcome the 0 still leaves a blank return
-
@Paul Newcome in thinking about it, I think I need something that tells the formula to match the left side's entry with the right side, which I believe is the Work Request Number, how would I add that to the formula?
-
That would be the MATCH portion of the formula. Which column is Range 1?
Are you able to flip it so that they populate at the top? That would make thing s much easier when trying to pull the data over.
-
=INDEX({Work Request Monthly Update Repository Range 3}, MATCH(MAX({Work Request Monthly Update Repository Range 1}), {Work Request Monthly Update Repository Range 1}))
Range 3 is Status Notes
Range 1 is Update Month
Where could I fit in the matching of the Work Request Number?
And yes, I could flip it if it makes this work ;)
-
To match on two criteria, you would actually need to use an INDEX/COLLECT which would in turn require a helper column with an additional formula since you can't nest a COLLECT function inside of another COLLECT function.
But...
The way the MATCH function works when evaluating a single column is from top to bottom. Once it finds the first match, it stops looking and returns that row number. If you have new forms being populated a the top, then the most recent will be the one picked up by the MATCH function.
Then we can stick with the INDEX/MATCH and only have to match on the work request number.
=INDEX({Range to pull from}, MATC([Work Request Number]@row, {Other Sheet Work Request Number}, 0))
-
@Paul Newcome ok, let me work on reversing the sheet and then will try the formula you've provide above...
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!