Thread: Inventory spreadsheet formula help

Hello all!

I'm working at a university department and am in charge of handing out keys to grad students. I also take them back at the end of their term here.

I'm trying to clean up our previous system (of pen and paper and hoping that we don't run out of keys before the busy fall season) and keep an inventory log as well as sign out log.

This is what I would like to see in my spreadsheet:

• I want to have columns of all the key numbers and their quantities.
• On the same sheet I'd like to have columns of the student's name, key number checked out/in, and quantity.
• I need to produce a formula that when a key number is checked out, it subtracts the quantity from the corresponding key number above, and vice versa for when it's checked back in.

Here is a screen shot so you can get my gist

screenshot.jpg

Hope you can help!
Jen

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

Sorry for the delay - I'm new to this forum and thought that I would be emailed when replies came in. Anyway, attached is the file I have set up. I put comments in the first "Quantity" cells of the Check Out/Check In columns.

Thanks again for any help!
Jen

Key Inventory.xlsx

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.