Asset Management - Best Practice

Mark Hibyan

Hello Smart People -

I’m building a simple asset management solution. I want to track assets as they move between locations using barcode scans only. I have a “live” sheet that has the current locations, updated as each asset is scanned into a new location. Each asset is only listed once here. I then have an “archive” sheet that keeps track of all histories – copied from the “live” sheet every time the location of the asset changes, and records a date / time stamp (creation date).

 What I’m struggling with now is the best way to organize this to easily see A) the lifecycle of an asset (time spent at each location), and B) the average times all assets spend at each location (e.g. Location A = 6.2 days, Location B = 3.8 days etc.). I can create filters on the “Archive” sheet to easily see the assets as they update, but I want to see duration (days) at each location - and summarize that into some metrics. Any ideas on best practices here? Creating an archive sheet for each asset is not manageable, and I'm struggling with the formulas to summarize the time spent per asset, and by location.

Thank you!

Mark

Mark Hibyan

I answered my own question here using formulas to identify the previous location of an asset and the time it spent at that location. I then created a "staging" sheet where those formulas process data, and then copied the static values into a final "archive" sheet - which I base my reports off of.

Mark Hibyan

I answered my own question here using formulas to identify the previous location of an asset and the time it spent at that location. I then created a "staging" sheet where those formulas process data, and then copied the static values into a final "archive" sheet - which I base my reports off of.

Genevieve P.

Hi @Mark Hibyan

Thank you for posting your solution! I'm sure this will help someone else looking to achieve the same thing.

Cheers,

Genevieve