Calculating total using IF and VLOOKUP

Hi There,

Can anyone perhaps help me with an advanced calculation formula.

I have attached a dummy file as reference and example on my querie.

I want to be able to calculate the remaining stock levels using a formula that will lookup the name of the product and them subtract the amount of stock that has been dispatched. I would also like the same sheet to be able to lookup the products and calculate the stock levels when there is stock received.
I have three tabs on my sheet.
Tab one will be the day-to-day stock in and out bookings which is linked to the other sheets with VLookup to input some data when they input data into the first column. Then they will input the product and then the amount in or the amount out.
The second tab is my stock level sheet that I want to link to sheet one. When stock is received, I want it to add up for that specific product received. And when stock is dispatched, I want it to subtract the amount for that specific product.
Tab three is just information for the other sheets (they are linked with VLookup)
I am not too clued up with formulas, I know I few, but I just don't know how to get this one to work.
Any other suggestions will also be helpful, if this is not possible to work.

Thank you, Gisela

Where did the values in H4 and H10 come from????

Take a look here. There are 6 new formulae in the sheet. Each differnet formula has a different shading. I added an extra column for initial stock. How does this look?

See the attached sheet for formulae and context.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.