Scheduling formula

mdean32

New Member
Joined
Jan 15, 2019
Messages
22
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]
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi,

Your "Total qty", is that supposed to be a Running Total?
 
Upvote 0
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.
 
Upvote 0
So the Total qty is supposed to Stay unchanged as you have it?

If that's the case:


Book1
ABCD
1Open QtyRod ManufacturedH Rod QtyTotal qty
28001166H62A0775089659765
310001166H62A0775079659765
415001166H62A0775064659765
510001166H62A0775054659765
63001166H62A1212537194019
729001166H62A121258194019
Sheet477
Cell Formulas
RangeFormula
C2=D2-SUMIF(B$2:B2,B2,A$2:A2)


Formula copied down.
 
Last edited:
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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
10Open Qty
11=TRIM([rods.xlsm]Sheet1!F15)+0
Sheet477
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top