Pull Last Entry from One Sheet to Another Based on Criteria

Jonna Critchley

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?

Paul Newcome

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

How is the Monthly Update Sheet populated?

Jonna Critchley

@Paul Newcome it is populated by the chairs submitting a monthly update by Smartsheet form

Jonna Critchley

@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).

Paul Newcome

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.

Jonna Critchley

@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?

Paul Newcome

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))

Jonna Critchley

@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?

Paul Newcome

Is the form populating at the top or the bottom of the sheet?

Jonna Critchley

@Paul Newcome bottom

Jonna Critchley

@Paul Newcome the 0 still leaves a blank return

Jonna Critchley

@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?

Paul Newcome

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.

Jonna Critchley

=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 ;)

Paul Newcome

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))

Jonna Critchley

@Paul Newcome ok, let me work on reversing the sheet and then will try the formula you've provide above...