Excel Conditional sum, lookups and functions help PLEASE!!

[Guest]

Hi

I have a question I'm hoping people here can help with. I'm going to try to
be as clear as I can !!! If it's not clear please tell me!

In tab one on a worksheet (called "Assetts") column D holds the assett
number for a product (D2 being the first number). Sample below :

Description Date Rec Supplier Prod No Purchase Price List Price Qty

On tab 2 (called Orders) column A holds the product number that has been
order (i've then got a formula which enters the price I paid for the
product). Column C on the same tab holds the quantity of products ordered for
the product number entered in column A tab 2. Below is a sample :

Product ID Date Sold Quantity Req Sale Price Customer ID Price
6825 21/03/2006 1 £4.99 5 £4.99

Now for the question - when I enter the product number in Colum A, tab 2
(orders), I
would like to :

1) when the prodcut ID is entered in tab 2 (orders) , a lookup is run to
check that
number in the assetts tab, col D

2) then checks the quantity in tab 2 col C,

3) and then removes that quantity from the assetts tab col G

or some other idea if anyone has any!!!!!

Sorry if this is asking too much but thanks for any help in advance.

Bill

 

[Pete_UK]

You could use either MATCH( ) or VLOOKUP( ) to see if the ProductID in
the Orders sheet exists in column D of the Assetts sheet.

An INDEX( ) function around the MATCH( ) function, or just a VLOOKUP(
), could be used to return the quantity in the Assetts sheet, to ensure
that Quantity Rec in the Orders sheet does not exceed it.

A function can only pull data from somewhere else - it can't "push" it,
so you would need a different approach to achieve your point 3.

Hope this helps.

Pete