Forum Discussion
Real key management using excel
a) I don't know if there is already something out there that is ready to go or close to it but I would search for Excel Inventory Management to see
b) For DIY, I would recommend creating 3 (or maybe more) tables:
- a key log table to check out and check in the keys. basically a date/time, key ID, an inventory change value (i.e. +1 for check in and -1 for check out but you could use the words "out" and "in" and then in the name manager assign +1 to "in" and -1 to "out")
- a key registration table with every key ID, quantity, and some info on what that key goes to
- a key inventory table that uses either a pivot table, power pivot, or formulas like FILTER to pull the net value from the key log and add that to the key registration to get a net inventory
Hope that helps and hope your other projects have come along.
- Hello Matt, when I saw your reply last night I was super tired and very surprised to see your reply. I thought you dropped off the map so you can realize how a taken back I was to see your name. And yes, it has been awhile. If and when you get a chance, there are few things I could use your help or insight on to figure out how to handle my need using excel.
Carl_61 quick sample attached.
I actually just added the inventory column to the master table.
and have to correct an error in my statement before, you can't just name "out" as -1 and use the text that way, that will only apply in functions. so I added a SWITCH statement to convert all "out" and "in" text to corresponding values but you could also just enter numbers so 1 line could check out 3 of the same key using -3.
see attached.
Matt, the attached is what I have and an trying to make the key inventory check in/out system. I am not apposed to changing anything as this is just a stab at it I was trying. The main thing is that the first 9 columns are retained. Obviously, the columns showing the Key Tag # and Door key numbers are important but they would be part of the inventory table and be subject to check in and out. The room numbers with out A or B attached are single person suites and there is only 1 key required to open the door. There may a qty of the key but only 1 key with duplicates. The rooms with A & B attached to the number are 2 person suites with 2 rooms. The main door key only open the main door whereas the room keys open the main door and the room key associated with the room the individual resides in. Not many Hard Keys are issued as the doors have swipe keys but when the swipe key does not work, a hard key may need to be issued.
m_tarler wow, Matt. Thanks for chumming in. I like your thought on the DIY but I do not know anything about piviot tables, power tables and only a little about filter. I would more of examples to what you are speaking about on how to create the tables you spoke of. Great to see you back and I sure could use your help.
carl