Running total VBA

I am creating a spreadsheet for inventory use. I want to have a running total in (1) cell based upon a new/different number being entered into a different cell.

Column B, Row 1 (This will be a new/different number entered every day - inventory in or out, so positive or negative number)
Column D, Row 1 (This will be a running total based on numbers inserted in previous 2 columns)

Basically what I have is a key inventory. So there is column A with key number, B should be keys IN, C should be keys OUT, or ideally B would be IN and OUT meaning, for inventory IN input a positive number and for inventory OUT input a negative number, and D Should be total.

Column E represents number of keys currently in the inventory. I was going to hide the current inventory column so all you see is IN/OUT and total.
So what I need is to be able to just come in and type in a number in the IN and/or OUT column, without having to add/subtract it with the number already in that column.

Bottom line, I’d like to be able to use IN and/or OUT columns to just type in numbers as they come and not have to worry about what’s already in those columns and get correct total number.

Can someone please help me with this?

Thanks!


Ok here it is. Attached worksheet shows
Column A - Key Numbers...No data value
Column B - Inventory IN
Column C - Inventory OUT
Column D - Total
Column E - Current Inventory(Starting point)

So the formula I used to get what i currently have is (=B2-C2+E2). This way whatever i input in columns C and C. totals out in D. But this way every time I want to add/subtract a number in B and C, i have to add to the number already in the column. I'd like to be able to type in a number in B and C as i go and still have a correct total. I wouldn't mind having just one column for in/out and use positive and negative numbers to differentiate inventory in or out.

I hope this makes sense. Thanks
Key Inventory - Test.xlsx

* Thank you so much! I'll try this!

Originally Posted by judgeh59

I took a different direction....I redid your data....now you can enter the key number, the date (Cntl+Semi-colon) and the transaction amount (Qty). You just keep adding to the that. Then the pivot table that is setup, all you have to do refresh and all your amounts are updated and you can group by months/years on the date column....this may not be what you are looking for, but I hope it helps. If nothing else to give a different look....have fun....

* Thank you so much! I'll try this!

@judgeh59, @TMS I am sorry if i created a problem. I was told that this was a different thread problem so I copied it to a different/more appropriate forum thread. I still haven't gotten an answer i'd like so you are not wasting your time. Again, I apologize if i am creating confusion.

Originally Posted by TMS

I appreciate your frustration. However, ...

If you had marked the original thread as Solved, or if you had provided links to the other threads, everyone would be aware that others might be working on a solution for you. You now have at least three people involved, independently, in trying to answer your question. That is wasting time and effort ... not least of all yours. It's all the same people looking at this stuff.

You didn't need to start a new thread, although I understand you might have thought that was what I was suggesting. Just saying that a VBA solution was acceptable would have been enough. Lesson for the future, for both of us. I should maybe just have asked the question

Again, I am sorry for the confusion. I am new on here and confused myself at what i am supposed to do. Thanks for your input, I appreciate it and as you said, good lesson for future. Thanks again.

Originally Posted by judgeh59

I took a different direction....I redid your data....now you can enter the key number, the date (Cntl+Semi-colon) and the transaction amount (Qty). You just keep adding to the that. Then the pivot table that is setup, all you have to do refresh and all your amounts are updated and you can group by months/years on the date column....this may not be what you are looking for, but I hope it helps. If nothing else to give a different look....have fun....

Thank you for your input, but unfortunately this is not what i need.
What i need is very minimal... I need to know how to make it where when i insert a number in a designated column the total updates accordingly.
The column i insert the number into, needs to be just independent if that makes sense, meaning, i can input numbers and next time input another one over it and it will still update my total regardless of what was in that column previously. So I need start inventory column, ever-changing column to input inventory in and out, and a total that gets updated as the numbers are imputed.

This is a key inventory where we are constantly taking keys in and out of inventory and i just need to be able to go in the spreadsheet and type in one in, 2 out for example and go on....

Is this something you could help me with? Is it even possible?

Thanks.

Does not the simple auto sum do the work

This is not an expert by expert though

Find attached , feed details on sheet 2 and sheet one is auto

Though it way beyond simple , but it does the work as to its simplest extreme ?

Originally Posted by makinmomb

Does not the simple auto sum do the work

This is not an expert by expert though

Find attached , feed details on sheet 2 and sheet one is auto

No, that doesn't work because i want to be able to input numbers in IN/OUT column without depending what's already in there.
So, have a start number column, IN/OUT column that is pretty much a blank column and whatever you input in adds to the current total regardless of the previous number entered, and then have a total column in which numbers change/update according to IN/OUT column input.

Bottom line, i just want to be able to come in type in 3 keys IN, 6 OUT, and the total updates without me having to add/subtract from what's already in that column.

I hope that makes sense.

Thanks.

Originally Posted by TMS

OK, I'm not hugely comfortable about this but, as you asked so nicely, here you go ...

Please Login or Register  to view this content.

Please see the attached updated workbook. Note that it is now a macro enabled workbook.


Regards, TMS

OMG! This is awesome! Thank you sooooooooooooooooooooooo much!
This is exactly what i needed! You are awesome!
Thank you, Thank you, Thank you!

TMS.....You are going to kill me...... I need your help one more time please?
What you gave me works amazing, but i don't understand macros and i am not sure how to apply what you made to the rest of the sheet.
Can I add 3 macros? Please see attached.

Thank you very much! Sorry to bother you again!