Hi

If you do away with having entries in your opening stock column, and
just make then entries in Shipping in,
and if you list your 20 product names in cells E1:X1

then in cell E2

=SUMPRODUCT(($A$2:$A$1000=E$1)*($D$2:$D$100))
-SUMPRODUCT(($A$2:$A$1000=E$1)*($C$2:$C$1000))
and copy across through F2:X2
--
Regards

Roger Govier


"vandy" wrote in message
...
Hi All,

Can anyone help me out with my query. i havent been able to decrease
and
increase my stock qty using the formulas given. It works perfectly for
a
single line item but i hav got more than 20 items per sheet and wanted
to
just add the shipped out and shipped in qty and have the current stock
change. I think i have to use circular reference can any one help me
out.

thanks in advance
vandy

"vandy" wrote:

Hello,

I am trying to calculate the stock inventory of items in my company.
I have
imported data from access into excel and attemting to calculate the
stock
increase and decrease value.

Eg


CURRENT STOCK = 200
ITEM SHIPPED OUT = 20

CURRENT STOCK = 220

ITEM SHIPPED IN = 100
CURRENT STOCK = 120

It should change according to the qty shipped our or shipped in and
should
show current inventory stock for each item in excel.

Can anyone please help with the formula to be used for the same.

thanks in advance.