Forum Discussion
Trying to create a workbook to track inventory. NEED a little help.
I am trying to create a spreadsheet to help track equipment assigned to our employees. Basically I created on sheet for the "warehouse" where we can scan in all the unique equipment barcodes. Then, I created a separate sheet for each employee.
I would like to scan everything into the "warehouse" sheet to establish inventory for the company as a whole. Then I would like to scan out different equipment to each employee. Each equipment number will already exist in the "warehouse" sheet. So, when a piece of equipment is scanned/added to an employee's sheet, I would like the neighboring cell (on the warehouse sheet) to indicate which employee that equipment has been scanned out to.
I fell like this would be fairly simple for someone more advanced, I am just struggling with it. Also, if it makes more sense I am ok with just building this all in one sheet. The only reason I wanted separate sheets per employee was so we could print and have them sign for everything scanned to them that day.
Any help would be awesome. Thank you!!
8 Replies
- Kind of sounds like you need a warehouse management system. Excel won't scale so well the more complicated things get with parts/categories/people, you'll eventually spend more and more time managing the workbook.
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
- I uploaded my project file. I just sort of pieced together some formulas I found, as well a a macro to generate the exact date/time whenever a new piece of equipment is scanned into the first column.
