Index Max for only the last entry

Julzbrownlie

I have a sheet for all our vehicles and on that sheet it requires the mileage for the vehicle and the date the mileage was updated. Each day on a separate sheet the drivers complete a daily defect recording both the date and the mileage.

I would like to populate the mileage and date from the daily sheet to the overall sheet always giving me the latest mileage for that vehicle.

We record the registration on both sheets.

Victoria_Indimar

Hi @Julzbrownlie!

I think you would need to use a combination of Index, Match, Max, and Collect.

Collect the Dates of the Vehicle Registration in your daily log where Vehicle Registration equals the Vehicle Registration in the current row of the main log. Then you take the MAX so you know which is the latest date. Something like =MAX(COLLECT([DATE]:[DATE],[VRDaily]:[VRDaily],=VROverall@row))

I would add a helper column in the daily log to have VR+Date. Then you can INDEX the Mileage in the daily log where the VROverall+MAX Date matches the helper column VR+Date.

=INDEX([Mileage]:[Mileage],(MATCH(VROverall+MAX(COLLECT(…)), [HelperVR+Date]:[HelperVR+Date],0),1)

Hope this makes sense and helps!

Julzbrownlie

I am struggling with the first part what is the VROverall

Paul Newcome

Are you able to provide some screenshots for context?

jmyzk_cloudsmart_jp

Hi @Julzbrownlie

Suppose your Daily Defect Sheet is like the one below.

https://app.smartsheet.com/b/publish?EQBCT=ed3d96abf575491d8d40c964bd56930a

In the Vehicle Master sheet, I used the following formula to populate the Registration numbers dynamically.

[Registration] =IFERROR(INDEX(DISTINCT({Daily Defect Sheet - Registration}), [No.]@row), "")

https://app.smartsheet.com/b/publish?EQBCT=ae3a7247bf404261ab19fb05e308cf6a

Then, with the Registration data, I used the same logic as @Victoria_Indimar using the MAX(COLLECT()) functions. Mine uses cross-sheet reference, but the logic is the same.

[Latest Mileage Date] =MAX(COLLECT({Daily Defect Sheet - Date}, {Daily Defect Sheet - Registration}, Registration@row))

With the "Latest Mileage Date" and Registration data, I can get the "Latest Mileage" with the INDEX(COLLECT()) functions.

[Latest Mileage] =INDEX(COLLECT({Daily Defect Sheet - Mileage}, {Daily Defect Sheet - Registration}, Registration@row, {Daily Defect Sheet - Date}, [Latest Mileage Date]@row), 1)