Door and locker keys inventory

Refugio E. Cruz

Hello to the community,

I am trying to figure out a solution for keeping track of Keys that are handed out to employees in our building.

I have one sheet with all the key names and the number of each key.

Ex.. Front Door , 5 keys

On another sheet I have the persons name, email, the name of the key they have requested and a column with a key delivered to with yellow(pending), red (not given), green (given) dots.

The issue I am having is finding a formula to subtract a key from the first sheet when it has been entered as approved and delivered on the second sheet. This way I know how many keys have been handed out and how many keys I have left.

Any help would be greatly appreciated.

James Keuning

I will call your sheets Sheet A and Sheet B:

Sheet B:

I would personally use a date field in the Key Delivered field and the Key Returned field. And I would create a field called Key Out. In Key Out, put this formula:

=IF(AND(NOT(ISBLANK([Key Delivered]@row)), ISBLANK([Key Returned]@row)), [Key Name]@row, IF(AND(NOT(ISBLANK([Key Delivered]@row)), NOT(ISBLANK([Key Returned]@row))), 0, 999))

Then, in Sheet A, we will use a cross-sheet formula to tally the Key Out field.

In Sheet A, use this formula in Assigned Keys:

=COUNTIF({Keys B Range 1}, [Name of Key]@row)

Where {Keys B Range 1} is column Key Out in Sheet B

In Sheet A you can also use a formula in your "Return Keys" field, which I think is the number of keys available:

=[Total Keys]@row - [Assigned Keys]@row

But that's super simple.

If you want to use Red and Green in Sheet B, you can create a column with this formula:

=IF(NOT([Key Out]@row = 0), "Red", IF([Key Out]@row = 0, "Green"))

Refugio E. Cruz

Hello James,

Thank you for your answer. However the formula in the key out, that you suggested does not work. I get a #unparseable error. Also does the formula take the key name from the column, because I have to be able to keep track of the individual keys according to their names, when I am tallying the keys.

Thank you.

Ref