Most recent date for specific asset
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.
Comments
-
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
-
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.
-
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.
-
You are correct. You cannot use a report to populate a sheet or in cross sheet references for pulling metrics.
-
Hi Paul,
It is perfect, thank you very much!
Sana.
-
nice idea paul
Richard
-
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives