Most recent date for specific asset

Sana Salem Constant
edited 12/09/19 in Smartsheet Basics

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.

 

C5 Calibration Log-extract.png

C5 Calibration Schedule-extract.png

Comments