Door and locker keys inventory

Options

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.


Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    Options

    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
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!