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 and be able to check out a key or keys as necessary. I have main door keys and keys that fit room A & B room doors. So that means there is a main door and 2 bedroom doors with a suit with separate keys. There are main door keys that only work on the main doors and then there are individual room door keys that open the main entry door and the assigned bedroom door. I need to be able check a hard door key out which in turn reduces the available on hand inventory but also knows a key or keys are checked out. Does anyone know of something already working that could be altered if necessary to fit my needs?
Carl
- 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.
