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.

C5 Calibration Log-extract.png

C5 Calibration Schedule-extract.png

Find more posts tagged with

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Dashboard Adding Widgets Spontaneously
Howdy, I am working on developing a several dashboards for our team, and for whatever reason the widgets I place are multiplying. I have copied/pasted some, but other I have just added I am ending up with dashboards that look like this: When originally I only had the top four items. It has happened on several different…
Project Management with tracking costs
I am a construction project manager and built sheets that include actual costs for individual tasks, departments and overall project. I update the costs cells as invoices come in by manually calculating then entering in new amount. Ex: Plumbing: week 1 $300, week 2 $250, week 3 $150 = $700. I enter $700 in cell. I'm…
Showing "stacked" data / Statuses of items by swimlane
I have multiple items that each have their own statuses and that roll up to "workstreams" or swimlanes. On the dashboard, I would like to show the progress of the items by swimlane without breaking it out into separate widgets for each. This would ideally be a stacked bar where each bar represented the Items and was broken…