Forum Discussion
Real key management using excel
- Hi Carl, it's been a while 🙂 I've been AFK from the community due to IRL work obligations (and on a new account too). but today I needed a break and thought I'd drop in. I can't commit much time right now but to answer your questions.
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.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
- 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.
