Need to Return Latest Update in Repeating List of Assets

Andrew Ryba

We have a set of Hardware Assets that we use as a "Check Out/Check In" system in Smartsheet. Our employees can check out a piece of hardware and check it in when they are done using it. We use a form to intake the request and update the asset as out, or returned when the asset makes it back in.

In this example, I have 5 total assets and I am looking for a way to return the latest update/status for each of the 5 Assets. For each Asset I want to know; Who Has it (Checked out by), the date it should be back (Date In), and Status.

Thanks!

Paul Newcome

If you set it so that new forms are populated at the top of the sheet (and reorder the existing), then you can use

=INDEX({Form Sheet Column to Pull From}, MATCH(Asset@row, {Form Sheet Asset Column}, 0))

Andrew Ryba

In order to provide this value back to the sheet, it looks like I am going to need a column for each of the assets, is that correct?

It looks like I can only search for one value per formula. I was hoping to be able to use a column to filter off of and report back to a dashboard.

Thanks for the quick look!

Paul Newcome

I'm not sure I understand. Are you able to provide a screenshot of manually entered data that shows exactly what kind of output you are looking for?

Andrew Ryba

It is definitely a confusing concept to try to explain in text. I hope this helps. The top option is an attempt to clarify my question. The bottom is a possible alternative in presentation.

Paul Newcome

Ok. If you set up a sheet that has the same columns as in the second screenshot, you would manually enter the asset name, then use the above formula in each of the different columns where you want to pull the data to. You would only need to update that first cross sheet reference to pull from the appropriate column in the first sheet. That is assuming you sort the current form entries so that the most recent is at the top of the sheet and adjust the form settings so that new entries populate at the top of the sheet.