Forum Discussion
Real key management using excel
Carl_61 attached BUT by looking up the name that means if the name in the ALL tab changes it will APPEAR that someone else checked it out. for example one year to next or someone leave and new tenent comes in. Similar issue with keys if they lose key or prior doesn't return key... just things you need to consider.
- Oh ok. I did not realize. Looks good though. Just tested it. Thumbs up! Thank you. Carl
just saw your PM. I thought I replied but either it didn't take it or I forgot. in the attached I changed the log to be Resident Room and Door so "Resident Room" should be their specific room with the "A" or "B" if applicable and then Door is main door, room A or room B.
- Matt an idea just came to me about the key issue I told you I would think about. How about adding a column on the Key Log to the right of the room number. As we know, all suites or rooms have a 3 digit number. Some them have an additional character such as an A or B. If a 3 digit number gets entered it is known to be for the main door. If a 3 digit number gets entered with an A or B it is known to open the A door or the B door. In the case of suites with A & B rooms whereby a main door key needs to be issued 1 or both of the occupants, the added column could be where the designator can be placed to define which occupant received the main door key. So, 234, main door key, additional column A or B defines which occupant got the key. If it is the Key for the room, simply entering 234A would pull the key info for the room but just entering the 3 digit number would be the main door key for the suite and the designator for whom the key was giving would be based on A or B. What do you think?
- I just changed Key ID to Room and now the "Y 1" shows up. Thank you
- 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")) - 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. - Matt,
So here the formula I found in the previous post. The only exception is that I changed the tail end of the formula per what you stated above. The "NO" fills into all the cells when I dragged the formula down thru them but none of the cells in the "L" column give any indication of "Y 1" or so on. I do have 1 entry on my key log I would expect to be displaying "Y 1" but it does not. Is there more I am supposed to be changing in the formula?? - Ok Thanks, I am continuing to figure a way to keep this within the realm of formulas as my ability to use macros and VBA has been locked down.
I didn't post it because of the issues I noted. But essentially it is identical to the one attached to the Jul 20 post above except instead of returning 0,1,2... it reformats it to "NO", "Y 1", "Y 2", ...
If I recall correctly that formula has a LET(.... , net) and I changed it to LET(..., IF(net>0, "Y "&net, "NO"))just saw your new msg and possible, yes but it would require VBA/macros running (which as you know I can do but I have been discouraging based on the direction excel has gone for security reasons)
- m_tarler,
Matt, just a thought but do not know if it plausible. using a formula, is there a way, when only a suite# is entered, for pop up window, like a message box, to appear prompting for selection of Room A or B, to select which occupant is checking out the suite key? This of course only to happen when there are rooms A or B involved. Not needed for rooms that don't have A or B within. Hello Matt,
I see what you are saying and I'm trying to think of a way to deal with this issue. Also, in your last response you mention having updated a formula and doing something with NO or Y 1, Y 2 and so on but I cannot find that updated version so I can test and think about the issue. Have you posted it?
Yes, I've thinking about that issue too.
Carl
- so i'm having an issue. the hard keys inventory has suite #s and when appropriate room#s. The log correspondingly uses a specific suite/room number (i.e. 201 is suite key while 201A is room) and then the ALL tab only lists 'rooms' (i.e. 201A and 201B but NOT suite 201). So you want a name to get pulled from this ALL tab but if you enter 201 will it bring up 201A or 201B name? furthermore, you want the column L on the ALL tab to look up the keys checked out for BOTH suite and room but how do you know if 201A or 201B checked out that 201 suite key....
i updated the formula to display NO or Y 1 or Y 2 or ... but that is only effective on the specific room #s due to the problem above. - Matt, thank you for your feed back. I did not take any of your commentary sideways nor would I. I look to you for help and appreciate your help. Like I said, I don't know what I don't know. I appreciate you being straight up with me. If there is a way to accomplish what I am trying to achieve other than what I am seeing or understanding, I am all for it. This is why I always say, If there is a better way to achieve what I am after, I am all ears". I am not afraid to ask for help because 1, When it comes to what you are doing for me (I have NO CLUE) in most cases and 2 my way of thinking may not be the correct way of thinking. So, thank you.
So, when it comes to the Reported Value on the ALL Tab, can that data be collected per the way this Inventory or Key Log capable of arriving at a "Y" or "NO" along with the number of checked out key?
Carl - my reply is at the top of this page (at least for me) and was on Jul 20 2024 10:25 PM just 5 min before your post at 10:30 so maybe you didn't notice I had sent a reply?
I know you appreciate it and wasn't trying to say otherwise, but as you mentioned you know or think you know what you need. In no way do I mean that disrespectively but just that we ALL tend to follow the old adage of 'when all you have is a hammer everything looks like a nail'. Let me give you a practical example from this forum, we get a LOT of people (accountants usually) that want help fixing their spreadsheets but the sheets are set up like the physical paper tally logs they are used to. When you have to do things by hand you want to organize the entries as you enter them so you don't have to do double work. But when you use a computer/Excel it is just the opposite and you want to let the computer do that work.
So input tables should be just input tables. That doesn't mean a lookup value in that table isn't out of the question, for example it could be useful and helpful during the data entry to show that the value they put in corresponds to XYZ either because it isn't obvious or as a confirmation, but typically shouldn't be done instead of entry. If it isn't needed then it is just redundant (it is a look up value so by definition redundant) and in the case of the Name in your case I feel may be at the cost and loss of an opportunity to collect some valuable information.
I don't disagree with the reported value on the ALL tab, it was mainly the name in the log that I'm concerned with. As you mentioned "When the turn in happens it may be ... the individual has vacated the suite/room, rendering the space to be vacant now and the name of the occupant gets removed from the roster" and presumably the next tenant's name gets added, so the LOG doesn't know/care and will a) have NO name when the 1st tenant name is deleted and THEN b) will list all entries for that particular key (i.e. prior entries) with the NEW tenant's name once that new name is added. A lookup is LIVE so it isn't 'locked' on entry, it will change when the data changes. Matt,
First and foremost, THANK YOU, Thank you for the help your are giving me to make my stuff work. I can envision things and how they should work but on most of this excel stuff I don't have what it takes to make it happen. And for that I am extremely Grateful. I don't know all the common protocols regarding development, only for the most part know what I need or think I know what I need. I know for every action there is a reaction, either positively or negatively.
BTW, I'd like to see and test the part with the names added but there is no attachment for me to grab.
In the case of the Keys I know that if a key gets issued (Checked Out) to an individual, its expected to be checked back in eventually. So while the individual has a key or keys I am trying to establish a way to, at a glance, know the individual has a key or keys. So with this said, column "L" on the ALL Tab (Hard Key Issued) is where if possible I'd like to see either "NO" or "Y" with a number that represents Yes, the individual has a hard key and the number represents how keys the individual has. So if the individual has a main door key only, "Y 1". If the individual had a main door key and a bedroom key "Y 2". If you were to see how this is happening in column "K" (Storage Locker) it may get better idea of what I am trying to accomplish. When the key gets checked back in the idea was the check in would offset the check out bringing the individual down to no keys checked out for the individual. When the turn in happens it may be a point to when the individual is just returning the hard key due to the individual swipe card lock having been fixed or the individual has vacated the suite/room, rendering the space to be vacant now and the name of the occupant gets removed from the roster. My roster is outside of the system we use for checking people into and out of rooms but it does not manage keys or storage lockers.
Carl
- as I stated before and will state more clearly here, I think that Name field in the check out/in log should be manual entry. I think the person filling that in should check ID and enter their name for accountability. having that as a lookup defeats any level or accountability (w/r to that log) and just adds redundant information. Although I did it for you in that attachment, I disagree with it and feel it also goes against the principle of sheet design where inputs should be inputs and outputs should be outputs, but in the popular saying, 'the customer is always right' so I did provide it. or at least I thought I did, so don't understand what you need beyond that.