Scheduling formula

[mdean32]

All 4 cells are using the TRIM function from other workbooks. I need H Rod QTY to subtract the Open QTY from the Total QTY each time the specific Rod Manufactured comes up on the schedule.

The 2nd part is, after getting the above, how could this function calculate by the earliest date first, over a 5 day period, with multiple departments running the same Rods Manufactured?

The 2nd part may not be doable, that's ok. I can make it work with the first question. Thanks


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 594"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Open Qty[/TD]
[TD]Rod Manufactured[/TD]
[TD]H Rod Qty[/TD]
[TD]Total qty[/TD]
[/TR]
[TR]
[TD]800[/TD]
[TD]1166H62A07750[/TD]
[TD] [/TD]
[TD]9765[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]1166H62A07750[/TD]
[TD] [/TD]
[TD]9765[/TD]
[/TR]
[TR]
[TD]1500[/TD]
[TD]1166H62A07750[/TD]
[TD] [/TD]
[TD]9765[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]1166H62A07750[/TD]
[TD] [/TD]
[TD]9765[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]1166H62A12125[/TD]
[TD] [/TD]
[TD]4019[/TD]
[/TR]
[TR]
[TD]2900[/TD]
[TD]1166H62A12125[/TD]
[TD] [/TD]
[TD]4019[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Your "Total qty", is that supposed to be a Running Total?

 

[mdean32]

No, the Total QTY is the inventory on hand at the beginning of the schedule. H Rod Qty will be the running total per Rod Manufactured.

 

[jtakw]

So the Total qty is supposed to Stay unchanged as you have it?

If that's the case:

Book1
	A	B	C	D
1	Open Qty	Rod Manufactured	H Rod Qty	Total qty
2	800	1166H62A07750	8965	9765
3	1000	1166H62A07750	7965	9765
4	1500	1166H62A07750	6465	9765
5	1000	1166H62A07750	5465	9765
6	300	1166H62A12125	3719	4019
7	2900	1166H62A12125	819	4019
Sheet477

Cell Formulas
Range	Formula
C2	=D2-SUMIF(B$2:B2,B2,A$2:A2)

Formula copied down.

 

[mdean32]

There must be something wrong with my cells. The Open Qty, Rod Manufactured, and the Total qty are all trimmed from 3 different worksheets. I assume this is messing up the H Rod Qty column formula. I have tried different functions, and the H Rod Qty always returns the total qty value. Thank you so much for helping.


[TABLE="width: 594"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Open Qty[/TD]
[TD]Rod Manufactured[/TD]
[TD]H Rod Qty[/TD]
[TD]Total qty[/TD]
[/TR]
[TR]
[TD]800[/TD]
[TD]1166H62A07750[/TD]
[TD]9765[/TD]
[TD]9765[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]1166H62A07750[/TD]
[TD]9765[/TD]
[TD]9765[/TD]
[/TR]
[TR]
[TD]1500[/TD]
[TD]1166H62A07750[/TD]
[TD]9765[/TD]
[TD]9765[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]1166H62A07750[/TD]
[TD]9765[/TD]
[TD]9765[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]1166H62A12125[/TD]
[TD]4019[/TD]
[TD]4019[/TD]
[/TR]
[TR]
[TD]2900[/TD]
[TD]1166H62A12125[/TD]
[TD]4019[/TD]
[TD]4019[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

When you say the values are from a TRIM formula, it sounds like the values are Extracted from a Text string, is that correct?

If it is, can you show That formula.

 

[mdean32]

Absolutely! Thank you so much


[TABLE="width: 1563"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Open Qty[/TD]
[TD]Rod Manufactured[/TD]
[TD]H Rod Qty[/TD]
[TD]Total qty[/TD]
[/TR]
[TR]
[TD]=TRIM([rods.xlsm]Sheet1!F15)[/TD]
[TD]=TRIM([rods.xlsm]Sheet1!G15)[/TD]
[TD]=I15-SUMIF(G$2:G15,G15,F$2:F15)[/TD]
[TD]=IFERROR(VLOOKUP(G15,[inv.xlsm]Sheet1!$A:$C,3),"")[/TD]
[/TR]
[TR]
[TD]=TRIM([rods.xlsm]Sheet1!F16)[/TD]
[TD]=TRIM([rods.xlsm]Sheet1!G16)[/TD]
[TD]=I16-SUMIF(G$2:G16,G16,F$2:F16)[/TD]
[TD]=IFERROR(VLOOKUP(G16,[inv.xlsm]Sheet1!$A:$C,3),"")[/TD]
[/TR]
[TR]
[TD]=TRIM([rods.xlsm]Sheet1!F17)[/TD]
[TD]=TRIM([rods.xlsm]Sheet1!G17)[/TD]
[TD]=I17-SUMIF(G$2:G17,G17,F$2:F17)[/TD]
[TD]=IFERROR(VLOOKUP(G17,[inv.xlsm]Sheet1!$A:$C,3),"")[/TD]
[/TR]
[TR]
[TD]=TRIM([rods.xlsm]Sheet1!F18)[/TD]
[TD]=TRIM([rods.xlsm]Sheet1!G18)[/TD]
[TD]=I18-SUMIF(G$2:G18,G18,F$2:F18)[/TD]
[TD]=IFERROR(VLOOKUP(G18,[inv.xlsm]Sheet1!$A:$C,3),"")[/TD]
[/TR]
[TR]
[TD]=TRIM([rods.xlsm]Sheet1!F19)[/TD]
[TD]=TRIM([rods.xlsm]Sheet1!G19)[/TD]
[TD]=I19-SUMIF(G$2:G19,G19,F$2:F19)[/TD]
[TD]=IFERROR(VLOOKUP(G19,[inv.xlsm]Sheet1!$A:$C,3),"")[/TD]
[/TR]
[TR]
[TD]=TRIM([rods.xlsm]Sheet1!F20)[/TD]
[TD]=TRIM([rods.xlsm]Sheet1!G20)[/TD]
[TD]=I20-SUMIF(G$2:G20,G20,F$2:F20)[/TD]
[TD]=IFERROR(VLOOKUP(G20,[inv.xlsm]Sheet1!$A:$C,3),"")[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Ok, you're Not extracting the values from a "longer/larger" text string, so I'm assuming you're using TRIM due to Leading/Trailing spaces in the Cells that you're extracting From the other sheets.

Try this, update your Open Qty formula as follows by adding +0 at the end:

Book1
	A
10	Open Qty
11	=TRIM([rods.xlsm]Sheet1!F15)+0
Sheet477

 

[mdean32]

Amazing! Thank you so much! It worked to perfection!!!!!!

 

[jtakw]

You're welcome, welcome to the forum, glad it sorted out.

BTW, just noticed you adjusted my "H Rod Qty" formula Cell references to match your data, looks like your data starts at Row 15, so change:

=I15-SUMIF(G$2:G15,G15,F$2:F15)

To:

=I15-SUMIF(G$15:G15,G15,F$15:F15)

To avoid future complications.