Hi
Can anyone help me:
I wish to create a very simple to use goods in/ out spreadsheet.

Each row will be a different part number.

Column A = Part Number
Column B = Amount of Goods In
Column C = Amount of goods out
Column D = Current Stock Held

What I want it to do is to add up the quantity each time goods arrive in one
cell (cumulatively)and then do the opposite when goods go out. Then subtract
Column c from Column B to give me a running total (Column D).

Anyone know the formula?

If I understand what you want... Excel formulas return results based on the
numbers actually in the spreadsheet. If all your purchases and sales are
entered in individual cells Excel can certainly do the math. But it cannot
do a running total of amounts in the cells and amounts that used to be in
those cells. You'd need a macro to do an 'accumulator'. (But for my money
it's better to have a complete history so you can recreate the current total
if needed).

--
Jim
"Marcus1" wrote in message
...
| Hi
| Can anyone help me:
| I wish to create a very simple to use goods in/ out spreadsheet.
|
| Each row will be a different part number.
|
| Column A = Part Number
| Column B = Amount of Goods In
| Column C = Amount of goods out
| Column D = Current Stock Held
|
| What I want it to do is to add up the quantity each time goods arrive in
one
| cell (cumulatively)and then do the opposite when goods go out. Then
subtract
| Column c from Column B to give me a running total (Column D).
|
| Anyone know the formula?
|
|
|

Hi

It's possible through VBA (you have to write worksheets Change event for
this), but it will be wise to think about it again! When you accidently type
in some wrong number, then there is no way to restore right number without
summing all quantities from original documents. Better enter all
transactions into separate sheet, with columns Date, PartNumber, Type,
Amount (the column Type can have values "In" or "Out").

On other sheet you'll have another table, with columns PartNumber, InStore
, where for every PartNumber, the amount for current moment is calculated
through formula like
=SUMPRODUCT(--(TransactParts=A2),--(TransactType="In"),TransactAmount)-SUMPRODUCT(--(TransactParts=A2),--(TransactType="Out"),TransactAmount)

Such design also allows you to create various other reports, p.e. the list
of all incoming or outcoming goods for selected month or year. And it is
much simpler to designe too.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"Marcus1" wrote in message
...
Hi
Can anyone help me:
I wish to create a very simple to use goods in/ out spreadsheet.

Each row will be a different part number.

Column A = Part Number
Column B = Amount of Goods In
Column C = Amount of goods out
Column D = Current Stock Held

What I want it to do is to add up the quantity each time goods arrive in
one
cell (cumulatively)and then do the opposite when goods go out. Then
subtract
Column c from Column B to give me a running total (Column D).

Anyone know the formula?