Lookup help on inventory

[omer]

Hi,

I am trying to keep a track of my inventory and wanting to use excel to
calulate profit and inventory control. Let em try to explain below:

Sheet 1 -> has product description, price I bought i for and quantity
Sheet 2-> I want excel to lookup the item and price /quantity when I
sell something and then subtract it from sheet 1.
Sheet 3 -> Calculate the net profit I made based on the difference
between my buy price and sell price for that item.

I am unable to attach a file, but if you can help shoot me an e-mail @
(e-mail address removed) and I will send the file

Thx

 

[Max]

Assume you have

In Sheet1, cols A to C, data from row2 down

Prod BuyP Qty
ProdA 1.5 10
ProdB 1.5 10
ProdC 2.5 10

In Sheet2, cols A to C, data from row2 down

Prod SellP Qty
ProdA 2 10
ProdB 2.5 10
ProdC 3.5 10

In Sheet3, the products are listed in col A, from A2 down

Prod Profit
ProdC ?
ProdA ?
ProdB ?

Put in B2:

=SUMPRODUCT(--(Sheet2!$A$2:$A$100=$A2),(Sheet2!$B$2:$B$100)*(Sheet2!$C$2:$C$
100))-SUMPRODUCT(--(Sheet1!$A$2:$A$100=$A2),(Sheet1!$B$2:$B$100)*(Sheet1!$C$
2:$C$100))

Copy down

Col B returns the Profit ..

For the sample data in Sheets 1 and 2, you'll get:

In Sheet3
Prod Profit
ProdC 10
ProdA 5
ProdB 10