Most recent date for specific asset

Sana Salem Constant

Hello,

I have a list of equipment with the calibration date and schedule regarding our internal requirement ( 3 month, 12, 24..): this sheet is my Calibration schedule.

I also have a form that employee will use to record each calibration operation related to lets say a serial number. This sheet is my Calibration Log.

What I want to do, is populate my Calibration schedule sheet with the most resent calibration date for a specific serial number.

I tried different formula :

Calibration date cell for SR 2=VLOOKUP([SR #]2, {Calibration Log Range 4}, 2, false)) but I have to sort the SR column in the Calibration Log manualy to get the most recent date.

 

So then I tried the MAX-VLOOKUP function to avoid this step wich make my sheet not robust because it is not automaticaly done:

Calibration date cell for SR 2= MAX(VLOOKUP([SR #]2, {Calibration Log Range 4}, 2, false)), but it don't work if I don't sort the SR column in the Calibration Log.

 

So my question is, is there a way to automaticaly sort the data for a sheet? That will provide me with the right date.

If you have any other suggestion... 

 

Thank you in advance. 

 

Sana.

 

Richard Rymill SBP

Sana

carefully think through your criteria and then create a report to filter and sort data from your main Smartsheet. That should get you what you want. 

Hope that helps!

Richardr@smarterbusinessprocesses.com 

 

Paul Newcome

I would forget about the VLOOKUP and instead go with a MAX/COLLECT.

 

=MAX(COLLECT({Date Column}, {Serial Number Column}, [SR #]@row))

.

What this does is collects all dates from the Date column on your first sheet where the serial number in the first sheet matches that of the second sheet.

 

Once it collects all of those dates together, it will then look for the most recent or MAX date.

.

This removes the need for sorting anything.

Sana Salem Constant

Hello Richard,

 

Thank you for your answer,  I already tried but it appears that we can't populate a sheet from a report. Am I wrong? 

 

Thanks.

Paul Newcome

You are correct. You cannot use a report to populate a sheet or in cross sheet references for pulling metrics.

Sana Salem Constant

Hi Paul, 

It is perfect, thank you very much!

Sana.

 

 

Richard Rymill SBP

nice idea paul

Richard

Paul Newcome

Happy to help!