Need to Return Latest Update in Repeating List of Assets
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!
Answers
-
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))
-
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!
-
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?
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!