On 2002-03-10 03:20, Sami wrote:
i was thinkin something like Vlookuup n IF state ments!>????> :S
Thank you.
Again, you were quite close.....
what you want your stock book to do is look at how many lots of product have been sold (and sum them together), so normally we'd use =sum
but in this case you need to differentiate between each type of product - ie you have a condition, which is where we'd normally use =if
Excel has a function called =sumif which combines the two
(I don't like your stock book layout as it doesn't leave any room for amounts of items actually sold, so I'm assuming you'd start with say 1000 in the total quantity column and then deduct automatic sales in this cell linked to your sales book)
the syntax for =sumif is as follows :
=sumif(types of items, specific item, quantity)
so as with the other example you solved, if your sales book has a named range where your productnames sit and another named range where your quantities sit and on your stock book you have a list of your products sitting under your product name column you should be able to do something like this (remember your filenames again though)
=sumif(salesbook.xls!productname,stockbook cell that contains your first product name, salesbook.xls!quantity)
be careful to keep your ranges in this formula the same size
this should give you the amount of sales for each, so deduct this from your starting quantities (say 1000) :
=1000-sumif(salesbook.xls!productname,stockbook cell that contains your first product name, salesbook.xls!value)
By the way, I'm not sure if anyone's told you but if you click on the button that says Fx you can browse all excels =functions and it takes you through them step by step... very helpful
Hope this gets you started (or finished)
Chris
This message was edited by Chris Davison on 2002-03-17 10:03 
