Forum Discussion
Trying to create a workbook to track inventory. NEED a little help.
I would suggest a simple history of all assigned equipment, regardless of employee. It's fairly easy for Excel to produce a single printed sheet for any one employee from that history, and a single history file is a LOT easier to maintain. But let's start with seeing a sample of what you have so far
- posted
OK. Here's one approach that does not use macros. It could be made more sophisticated to be sure, but I wanted primarily to show you how you could produce a list for any given tech at a moment's notice, showing what that person currently has out.
This way of doing things does rely on several of the recently released Dynamic Array functions, and therefore requires the newest version of Excel.
First I made a list of the techs, and designated it as a table. I then use SORT(UNIQUE(table)) to generate a list for creating a drop down selection cell. If you add a name to the bottom of the table, the SORT(UNIQUE picks up the new name and it goes through to each of the two spots where you are able to pick a name.
Right now, there's just history of assigning equipment out. Part of what could be added is a transaction where equipment is returned.
For now, however, just look at the other new tab, where you can select the name of a tech (Marcus or Jose) and see what they've been assigned, dynamically created.
If you pick a name of a person to whom nothing has been assigned, that too will be displayed.
The FILTER function can be extended so that it recognizes when equipment has been returned. As I said, this is just to show a new way--without macros--to approach this.
If it looks feasible, I'd be happy to extend it to the next step.
Here's a source to educate yourself on FILTER, UNIQUE and SORT: https://www.youtube.com/watch?v=9I9DtFOVPIg
- Thank you, this is so much better than what I was able to do. I like this format, however is there a way to automate the process instead of using a drop down to select which employee has each piece of equipment. We have about 10 or so pieces of equipment checked out to each employee every morning. So, using a drop down will be very inefficient and time consuming. We have a usb scanner to scan the barcodes directly into the spreadsheet, and would like to use that to scan out to each employee as well. Is there an "easy" way to recognize the change once it is scanned to an employee?
