Aug 31 2021 03:39 PM - edited Sep 01 2021 05:46 PM
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!!
Aug 31 2021 05:55 PM
Aug 31 2021 08:05 PM
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
Sep 01 2021 05:48 PM
Sep 01 2021 05:48 PM
Sep 01 2021 08:01 PM
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
Sep 03 2021 08:17 AM
Sep 03 2021 08:30 AM
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?
Sep 03 2021 11:28 AM