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 ...
oh wait i seem to recall i changed [key id] to [room] or something. It's on my other computer and can share later but the point is that:
name on log auto fill in doesn't work for suite keys with multiple people and besides, why even bother? If you want the log to be a record of who was issued the key then have them record who they gave the key to and not assume.
the second problem is related in that when you want to know the #keys tenant 201B checked out if you want to include the main suite keys then you need to know who checked that 201 main key out. if they enter the tenent name then it is much easier as you just filter based on their name. I can add a drop down that helps them find the name (data validation) instead of using a lookup that gives a name.
name on log auto fill in doesn't work for suite keys with multiple people and besides, why even bother? If you want the log to be a record of who was issued the key then have them record who they gave the key to and not assume.
the second problem is related in that when you want to know the #keys tenant 201B checked out if you want to include the main suite keys then you need to know who checked that 201 main key out. if they enter the tenent name then it is much easier as you just filter based on their name. I can add a drop down that helps them find the name (data validation) instead of using a lookup that gives a name.
Sorry, I forgot to post the formula.
=LET(change,FILTER(KeyLog[Out/In],LEFT(KeyLog[KeyID],9)=LEFT(C57,9),0),net,SUM(SWITCH(change,"In",-1,"Out",1,change)),IF(net>0,"Y "&net, "NO"))
=LET(change,FILTER(KeyLog[Out/In],LEFT(KeyLog[KeyID],9)=LEFT(C57,9),0),net,SUM(SWITCH(change,"In",-1,"Out",1,change)),IF(net>0,"Y "&net, "NO"))