The solution I gave you will do that, except:
1) Define the name Inventory to start with Prod No and include Qty
2) put this revised formula in the Assets Now column
=VLOOKUP(A1,Inventory,4,0)-SUMIF(A$1:A$100,A1,C$1:C$100)
(this assumes that the layout you've provided starts in column A)



"Billjary" wrote:

Thanks for the help so far guys but I think I missed a few areas off that
might mean these functions don't work. I have the following on a worksheet
called Assetts :

Description Prod No Purchase Price List Price Qty

I then have a sales tab with :

Product ID Date Sold Qty Sale Price Customer ID Price Paid Assetts now

I'd like, when I enter a product ID (number format), the sheet to
automatically check the Qty value of this prodcut ID, then take off the QTY
in the sales worksheet and enter the value in the Assetts now column.

Sorry if I was cryptic with the "oranges" but it sounded ok in my head!!! Am
I asking too much?

THanks for all your help so .


"Andy" wrote:

Hi

If your orders are in column C on sheet2 and your stock is in A2 on sheet1,
this:
=sheet1!A2-SUM(sheet2!C:C)
should give you the stock less the sales.

Andy.

"Billjary" wrote in message
...
Hi

I'd wonder if anyone can help. I've put my situation in to simple terms
below .......

I have a column in worksheet 1 which tells me how many oranges I have in
stock. When an order is presented, I enter the details on worksheet 2. Is
there anyway I can enter a formula to check worksheet 1, minus the number
of
oranges ordered in worksheet two, and enter the value somewhere in
worksheet
2? I'm thinking this would be a good way of telling me when I am low on
stock.

Thanks all - used some info on this site before - really brilliant!