Stumped: Inventory Value Usage

[Drunken_Sailah]

Hi,

I am trying to create an inventory spreadsheet where usage removes quantities from a lot until it is completely relieved and then the next lot is subtracted from. I have completed this as below:

[TABLE="width: 2097"]
<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Lot[/TD]
[TD]Date Received[/TD]
[TD]Qty[/TD]
[TD]Cost[/TD]
[TD]Beginning Value [/TD]
[TD]Current Qty[/TD]
[TD]Current Value[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]43101[/TD]
[TD]500[/TD]
[TD]60[/TD]
[TD]=D2*C2[/TD]
[TD]=IF(SUM($C$2:C2)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)<0,0,MAX(SUM($C$2:C2)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)))[/TD]
[TD]=F2*D2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]43132[/TD]
[TD]100[/TD]
[TD]70[/TD]
[TD]=D3*C3[/TD]
[TD]=IF(F2=0,IF(SUM($C$2:C3)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)<0,0,SUM($C$2:C3)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)),Product!$C3)[/TD]
[TD]=F3*D3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]43160[/TD]
[TD]200[/TD]
[TD]80[/TD]
[TD]=D4*C4[/TD]
[TD]=IF(F3=0,IF(SUM($C$2:C4)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)<0,0,SUM($C$2:C4)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)),Product!$C4)[/TD]
[TD]=F4*D4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]43191[/TD]
[TD]300[/TD]
[TD]65[/TD]
[TD]=D5*C5[/TD]
[TD]=IF(F4=0,IF(SUM($C$2:C5)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)<0,0,SUM($C$2:C5)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)),Product!$C5)[/TD]
[TD]=F5*D5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]43221[/TD]
[TD]200[/TD]
[TD]70[/TD]
[TD]=D6*C6[/TD]
[TD]=IF(F5=0,IF(SUM($C$2:C6)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)<0,0,SUM($C$2:C6)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)),Product!$C6)
[/TD]
[TD]=F6*D6
[/TD]
[/TR]
</tbody>[/TABLE]

Without the Formulas shown below:

[TABLE="width: 600"]
<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Lot[/TD]
[TD]Date Received[/TD]
[TD]Qty[/TD]
[TD]Cost[/TD]
[TD]Beginning Value [/TD]
[TD]Current Qty[/TD]
[TD]Current Value[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]$60.00[/TD]
[TD="align: right"]$30,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]$70.00[/TD]
[TD="align: right"]$7,000.00[/TD]
[TD="align: right"]100.00[/TD]
[TD="align: right"]$7,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3/1/2018[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]$80.00[/TD]
[TD="align: right"]$16,000.00[/TD]
[TD="align: right"]200.00[/TD]
[TD="align: right"]$16,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]$65.00[/TD]
[TD="align: right"]$19,500.00[/TD]
[TD="align: right"]300.00[/TD]
[TD="align: right"]$19,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]5/1/2018[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]$70.00[/TD]
[TD="align: right"]$14,000.00[/TD]
[TD="align: right"]200.00[/TD]
[TD="align: right"]$14,000.00
[/TD]
[/TR]
</tbody>[/TABLE]

The next step is the issue, however. I am also, on another tab trying to calculate the usage based upon the rows usage data, however, the same row keeps getting increased in usage value until a lot number is completely utilized. I want each line item to calculate the usage per row.

[TABLE="width: 242"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Ingredient[/TD]
[TD]Qty Used[/TD]
[TD]Total Cost[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]$30,000.00[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD] [/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD] [/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 578"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Ingredient[/TD]
[TD]Qty Used[/TD]
[TD]Total Cost[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]400[/TD]
[TD]=Product!$E2:$E$6-Product!$G2:$G$6[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]100[/TD]
[TD]=Product!$E2:$E$6-Product!G$2:$G$6[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD] [/TD]
[TD]=Product!$E$2:$E$6-Product!$G$2:$G$6[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD] [/TD]
[TD]=Product!$E$2:$E$6-Product!$G$2:$G$6[/TD]
[/TR]
</tbody>[/TABLE]

 

[Drunken_Sailah]

I see a lot of views, but, no responses, does this make sense or should I try to lay it out another way to try and get some help?