Hi - on Sheet "Stock" I have a 2 column 9 row table starting in C4. In cell C4 on April 1st I enter opening stock cost price and in D4 opening stock quantity carried over from previous year. This is repeated for each product.
As stock is purchased the cost price can fluctuate up and down so, if the cost price is different to a cost already in column C it goes in the next cell down and the quantity updates in adjacent cell

On a different sheet "Sales" I need to account for sales and currently list those by customer rows. In each row I'd like to calculate the cost of the order and sometimes the cost of product is more than 1 cost price eg - say customer Smith buys 1000 bottles of water. If I had opening stock of 400 at $0.95 bought 345 at $1 and 500 at $1.05 and sold them all at $1.20 the gross profit should be $207.25. I need a formula to calc the cost of $992.75 relating to the purchases and the different pricing.

The formula needs to know how many have been sold already so I have a cell in the row to tell me that, AL??
We use first stock in first stock out for calculating stock sold and stock left

I appreciate the help, thank you