Trying to create a workbook to track inventory. NEED a little help.

Copper Contributor

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

@CPI_LLC 

 

Please share a sample file

@CPI_LLC 

 

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

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.

@CPI_LLC 

 

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 SORThttps://www.youtube.com/watch?v=9I9DtFOVPIg

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas ...
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?

@CPI_LLC 

 

You'll have to ask that of someone else. I'd be just guessing, or, if I were with you, experimenting. I'd guess something is possible.....does the scanner's user manual point in a direction for how to do it?

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.