Evaluating the most recent date from one sheet and updating another

scott.95

Hi all, this one has me stumped. In the Last Service Date column for an asset, I'm trying to get the date of the latest service on that asset from another sheet.

I have a Sheet- Maintenance Log that has rows of maintenance activities for assets like air conditioners, fans, etc. I have a date from Sheet- Maintenance Log in a column Date Service Completed that tells me when the date was of the service. I want to take the most recent service date from from the Sheet- Maintenance Log and update an asset row in Sheet- Asset Tracker based on the asset number. Below is the formula I've tried. Any help is appreciated.

=INDEX(COLLECT({Sheet - Ongoing Maintenance-Date Completed}), {Sheet - Ongoing Maintenance-COE Asset ID Num}, ISDATE(@cell), {Sheet - Ongoing Maintenance-Date Completed}, [Latest Date]@row, {Sheet - Ongoing Maintenance-COE Asset ID Num},1)

Paul Newcome

The 1 is part of the INDEX function. In this case it was telling it to pull the first row it matched on.

But the MAX function pulls the largest value (or most recent date) so it is essentially "built in" and will cause that error.

Paul Newcome

It looks like you have a misplaced parenthesis. Try this:

=INDEX(COLLECT({Sheet - Ongoing Maintenance-Date Completed}, {Sheet - Ongoing Maintenance-COE Asset ID Num}, ISDATE(@cell), {Sheet - Ongoing Maintenance-Date Completed}, [Latest Date]@row, {Sheet - Ongoing Maintenance-COE Asset ID Num}),1)

scott.95

This gives me #unpareseable.

Paul Newcome

Can you provide a screenshot of the formula actually in the cell?

scott.95

Paul Newcome

It is not recognizing [Latest Date] as a valid column name.

I also had just copy/pasted your formula to correct the parenthesis issue, but looking closer at it, it looks like your overall syntax may be off. You are pulling in the Date Completed where...

ID Num is a date

Date Completed is equal to [Latest Date]@row

and then you don't have any criteria for the last range (ID Num again)

scott.95

Well bugger. Since I want the biggest or in other words the most recent date for an asset from the range that is on {Sheet - Ongoing Maintenance-Date Completed}, should I use a MAX formula with collect as shown below.

=max(collect({Sheet - Ongoing Maintenance-Date Completed}, {Sheet - Ongoing Maintenance-COE Asset ID Num}, [COE Asset ID Num]@row, 1))

Unfortunately, this gives me #incorrect argument set.

Paul Newcome

That syntax is more accurate in regards to the order of things, but you need to remove the comma-space-1 from the end.

scott.95

HALLELUIAH, halleluiah and thank you. BTW, what does the ", 1" do? I watch videos and read and it says to do it, but I don't know why. It is working fine now.

Paul Newcome

The 1 is part of the INDEX function. In this case it was telling it to pull the first row it matched on.

But the MAX function pulls the largest value (or most recent date) so it is essentially "built in" and will cause that error.