Another Scheduling formula issue part 2

mdean32

New Member
Joined
Jan 15, 2019
Messages
22
My current formula for "RODS AVAILABLE" is =IFERROR(G2-SUMIF(E$2:E2,E2,D$2:D2),"").

I have the table sorted exactly how I want to print it with the "Line" sorted first, followed by the "Run Date" in order. However, my formula for "RODS AVAILABLE" isn't correct by the Run Date needed. The "RODS AVAILABLE" function calculates by the line order instead of by the Run Date. How do I calculate "RODS AVAILABLE" by the Run Date, while still keeping the "Line" in the below order?

I am aware I can sort my table by Run Date and get the RODS AVAILABLE formula to calculate correctly, but I need my lines grouped together like the example below. Thank you for any help you can offer


A B C D E F G
[TABLE="width: 906"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Line[/TD]
[TD="align: center"]Run Date[/TD]
[TD="align: center"]SEQ[/TD]
[TD="align: center"]ORDER AMT[/TD]
[TD="align: center"]ROD[/TD]
[TD="align: center"]RODS AVAILABLE[/TD]
[TD="align: center"]ROD INVENTORY[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]1/21[/TD]
[TD="align: center"] 30[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A07250[/TD]
[TD="align: center"]2700[/TD]
[TD="align: center"]3200[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]1/22[/TD]
[TD="align: center"] 50[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A08000[/TD]
[TD="align: center"]1300[/TD]
[TD="align: center"]1800[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]1/23[/TD]
[TD="align: center"] 55[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A10000[/TD]
[TD="align: center"]700[/TD]
[TD="align: center"]1200[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]1/24[/TD]
[TD="align: center"] 60[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A10000[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]1200[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]1/21[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A10000[/TD]
[TD="align: center"]-300[/TD]
[TD="align: center"]1200[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]1/22[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A07250[/TD]
[TD="align: center"]2200[/TD]
[TD="align: center"]3200[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]1/23[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A07250[/TD]
[TD="align: center"]1700[/TD]
[TD="align: center"]3200[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]1/24[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A08000[/TD]
[TD="align: center"]800[/TD]
[TD="align: center"]1800[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]1/21[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A08000[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]1800[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]1/22[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A10000[/TD]
[TD="align: center"]-800[/TD]
[TD="align: center"]1200[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]1/23[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A08000[/TD]
[TD="align: center"]-200[/TD]
[TD="align: center"]1800[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]1/24[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A07250[/TD]
[TD="align: center"]1200[/TD]
[TD="align: center"]3200[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Can any Excel experts tell me if this is even doable? I've searched all over the internet looking for a solution. Thanks
 
Upvote 0
Not sure if I am getting you right.. But if you are asking for a solution like below mentioned table, then it is possible:

[TABLE="width: 200"]
<tbody>[TR]
[TD]Run Date
[/TD]
[TD="align: center"]Line
[/TD]
[TD="align: center"]SEQ
[/TD]
[TD="align: center"]Order Amount
[/TD]
[/TR]
[TR]
[TD]1/21
[/TD]
[TD="align: center"]13
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]500
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]14
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]500
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]15
[/TD]
[TD="align: center"]40
[/TD]
[TD="align: center"]500
[/TD]
[/TR]
[TR]
[TD]1/22
[/TD]
[TD="align: center"]13
[/TD]
[TD="align: center"]50
[/TD]
[TD="align: center"]500
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]14
[/TD]
[TD="align: center"]25
[/TD]
[TD="align: center"]500
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]15
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]500
[/TD]
[/TR]
</tbody>[/TABLE]

Please let me know we are on same platform..
In other case, if you are trying to sort the table with respect to 2 columns at the same time, I am afraid that's not doable as far as I know.
 
Upvote 0
Right now the formula is subtracting straight down the spreadsheet in the Rods Available column. For example, the spreadsheet is subtracting the 1166h62a10000 rods on dates 1/23 and 1/24 on line 13 before it's subtracting the 1166h62a10000 rods on 1/21 on line 14. I need all the lines to be grouped together like I have it laid out, but I need the Rods Available formula to subtract like values by date. Am I making sense? I can sort by date and everything is right (like your example), but the lines are not grouped together like my example.
 
Upvote 0
Hi,

Got your Tag, if I understand correctly, adjust the formula as follows:


Book1
ABCDEFG
1LineRun DateSEQORDER AMTRODRODS AVAILABLEROD INVENTORY
2131/21/2019305001166H62A0725027003200
3131/22/2019505001166H62A080008001800
4131/23/2019555001166H62A10000-3001200
5131/24/2019605001166H62A10000-8001200
6141/21/2019105001166H62A100007001200
7141/22/2019255001166H62A0725022003200
8141/23/2019155001166H62A0725017003200
9141/24/2019755001166H62A08000-2001800
10151/21/2019405001166H62A0800013001800
11151/22/2019305001166H62A100002001200
12151/23/201955001166H62A080003001800
13151/24/2019455001166H62A0725012003200
Sheet478
Cell Formulas
RangeFormula
F2=IFERROR(G2-SUMIFS(D$2:D$13,E$2:E$13,E2,B$2:B$13,"<="&B2),"")


Formula copied down.
 
Upvote 0
You're welcome, thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,021
Latest member
pingpong7117

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