The attached spread sheet has been adapted for security reasons but the original one is a lot bigger but the attached does give an example of my problem.
We recycle ink cartridges, so we buy back empty cartridges and then refill with an ink. So worksheet 'Stock' shows the amount of stock of empty cartridges and ink we have and also finished cartridges. 'Groups' shows what make up a finished cartridge ie ink + empty cartridge. 'Budgets' show the estimated sales.
'Order' is where my problem is - I can enter in dates here for the period I want to purchase in supplies for. All of the works fine for all of my products appart from where a product is used in more than one finished cartridge.
So in the example attached I want to order enough supplies in to make enough finished cartridges to last me up until the end of May 2012. In column D is ink type '1' and the answer in cell C6 will be incorrect because the sum is not allowing for the fact that the sum in C4 has already used up all of the available ink, also sometimes I could have the same ink used in three different finished products.
Can someone help me make the current sum allow for this please?