Asset Management - Best Practice
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.