Hi
I have a four column table (say A,B,C,D) comprising a ‘product description’, the ‘quantity ordered’ (integer value) ‘date ordered’, and quantity remaining.
Product Quantity Date Remaining
Product 4 5 04/01/13
Product 5 8 10/01/13
Product 2 5 23/01/13
Product 4 10 06/02/13
etc
I want column (D) to return the number of this particular product remaining after the order has been made (i.e. value entered into column B)
At the beginning of the year we tell the suppliers how many we items of a particular product we intend to order (a ‘call-off ‘system) and the total then ‘runs down’ during the course of the year.
There are only five products (let’s say Product 1, Product 2,…. Product 5) so the user selects the product from a drop-down list attached to each cell in column A (using Data Validation | Allow | List). This reference list (Product 1 .. Product 5) is in $W$10:$W$14. The adjacent column (X10:X14) has the ‘starting number ‘ (for each product) at the beginning of the year:
Product 1 19
Product 2 45
Product 3 25
Product 4 58
Product 5 89
It is easy to calculate the number remaining for a particular product the first time it is ordered, but I can’t work out how to deal with subsequent orders of the same product.
In cell D1 at present I have:
= VLOOKUP(A1,$W$10:$X$14,2,FALSE)-B1) … then copy down column D.
… but this obviously doesn’t work as the ‘reference table’ is not updated after each order.
Many thanks for your help.
Phil
Bookmarks