Excel Conditional sum, lookups and functions help PLEASE!!
 

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

Excel Conditional sum, lookups and functions help PLEASE!!
 

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