Working out stock levels and circular reference issues.

Hello,
This is my first post so go easy guys!

I am trying (without luck) to make a spreadsheet which looks at stocks levels for a potential business.

Now I need to be looking at the total stock level of a product which might have come from two different sources say A & B. So although the product is the same the margin levels are different.

So under sales I want to be able to add the amount sold and for excel to minus the amount from source A stock first and then if that stock level reaches zero deduct the remaining from source B stock.

At the moment I look at my orders sheet for a product from source A & B and so I know my individual stock levels. When I add a sale my Sales sheet looks at source A stock first before deducting the sold amount. But because my stock level is stock ordered minus stock sold it becomes a circular reference.

Now I could just manually enter the amount of stock sold in each sale up to the level of stock from each source but as the spreadsheet can work out how much before source A is depleted before subtracting from source B I wanted to be able to just add one figure in one Colum for total goods sold in each sale and work out the profit across both stock sources.

I have attached the spreadsheet for anyone to have a look. At the moment it has Sales sheet and Sales (2) sheet as I have been trying out a few solutions at the same time.

In Sales F2:F3 and Sales (2) E2:E3 I have managed to look at invoice numbers > then the current row which works for stock levels after sales are made but I can't reference these cells for stock levels for my =MAX(SUM( calculations in Sales G1:G2 and Sales (2) G1:G2 without them becoming circular references. I need this live stock level for the MAX(SUM to work out stock from each source.



As I am learning to use excel I suppose this is more about understanding how to fix or find solutions as opposed to a must have. Call it a wish list?