Forum Discussion
Real key management using excel
Hello Excel Family. I am a manager of 168 Dorm rooms for which I’d like to create an inventory & check out for hard door keys. I’d like to be able to have an inventory of keys on key tags with keys ...
Is the community telling me that you’ll don’t have any FCC
- Sorry me finger hit the wrong button. I’m just asking if anyone has any experience or knowledge to help me with this
- 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.- 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.
