Forum Discussion
Need help finishing this inventory file
Hi-
I've been working on creating an inventory log for my work. I manage a lot of spare keys, and they only get used if I have to replace broken keys on active sets, so I needed not only a physical Inventory of everything, but also an Activity Log.
The idea when I started making this, was that coworkers could pull up the file, select the key they wanted from the drop down menu, which would then populate relevant info like Bin# and the current on-hand quantity of that key, and they could then input a number in the In/Out column to either add or subtract an amount from that selected keys inventory. That amount would then update on the Inventory page under the appropriate key.
The idea is that the inventory page wouldn't have anyone directly messing with the numbers. It would update automatically when someone added a line to the Activity Log.
I was able to find tutorials on how to make a drop-down list. How to convert the inventory into a table. And how to use VLOOKUP to populate info for the Bin# and Inventory numbers.
But I cannot figure out how to link the In/Out cell to the drop-down key menu, and still leave it blank so that you can add/subtract amounts. I also can't figure out how to have the active Inventory numbers display as part of the drop-down, while also updating with the In/Out column. Using the inventory totals to both display an amount as well as using it to calculate is creating a circular formula loop and excel doesn't like it and won't let me do that.
I have spent hours searching tutorials and experimenting trying to get this last bit of the puzzle to work and I just can't find the information I need. So I'm hoping you guys can fill in the blanks and explain how I can make this work. I've been beating my head on this wall off and on for months now.
I'm attaching a copy of an inventory spreadsheet I modified a little over a year ago for another poster here. It shows one way of handling almost exactly the same kind of in/out activity you're wanting to track. And it also uses SUMIF to update the actual inventory, much as mtarler has suggested (fortunately) in his prior post.
See if you can pick up on the method suggested there and make modifications to your own. And by all means come back and ask questions as needed.
- I think you want the INVENTORY to be =SUMIFS( ActivityLog[In/Out], ActivityLog[Key], [ID #])
BTW i recommend formatting both sheets as TABLES and name the Activity Log something like ActivityLog (as I used in the example above) and the Inventory Log as Inventory
To make the Inventory log correct you may need to insert a set of initial values like:
Key1 ... +12
so that starting today it shows 12 of those keys in inventory
then Joe adds to the log and say Key1 ... -1 and the inventory goes to 11
That all said, know that the Inventory column on the ActivityLog will show the PRESENT inventory and not the inventory back when that log entry was added.
