Need help making inventory that updates quantities on different sheets

I'm creating an inventory workbook to keep track of supplies at work. The idea is that the supplies will be listed on their own inventory sheets (currently I have 4 sheets, as everything is sorted into their own categories) with current quantities, then there will be a main sheet that will act as an activity log. Any time someone wants an item, they'll go to the activity log, select that item from a drop down, then enter how many they want to remove or add. That amount will then be added or subtracted to the quantity listed on the inventory sheets.

I want to keep the inventory records as hands-off as possible to avoid inaccuracies with the counts. Ideally, anyone accessing the file will only have to input info on the activity log and inventory will update automatically.

I've figured out how to create a drop-down menu so someone can pick the supply they want. But I can't figure out how to set up the 'Quantity' cell. I don't want it to display inventory amounts. It should blank. It's just a cell for adding or subtracting. But I want it to detect the item that was selected from the menu, then when someone enters an amount (like -1 or +1), it automatically adds or subtracts that amount from the quantity listed in the inventory for that item.

If possible, it'd be great if the +/- cell on the activity log would change color based on whether you're removing or adding. Or, if the numbers would display as -1 or +1. I'm not fond of the accounting setting that puts parenthesis around numbers.

I was originally thinking of having a 'Remove" and "Add" column that would only subtract or only add the numbers entered in the respective column. But I couldn't figure out how to make that work either.

I'll use either option. Whichever is easiest to program and work with. The demo workbooK has examples of both ideas.

Any ideas you guys have would be much appreciated. Using Excel 2013. Thank you.

Hi and welcome

A couple of clarification questions.

Do you want the inventory sheet to be updated as users change the numbers (i.e. each time a row is updated the inventory sheet is updated?) or do you have another trigger in mind?

Is the activity log sheet to be an ongoing thing and the list just keeps growing?

BSB

Right click the "Activity Log" sheet tab and select "View Code". Then paste the below code in that sheets module.

Please Login or Register  to view this content.

Does this approach will hopefully work as you expect when you add +/- numbers to column D.

You could use conditional formatting on column D to colour the background based on + or - number entered.

Any use?

BSB

Try this:

Please Login or Register  to view this content.

If you had a table somewhere in the workbook that you could look up which sheet each ID# can be found on then you could make the code a little tidier as there would be no need for the looping.

BSB