Rolling Formula To Calculate Nets

[ahuang3433]

In my attached spreadsheet, I am trying to fill in Columns G:H.

Column G - The purpose of this column is to show inventory available once total demand of column D is deducted for each location.
Column H - Purpose is to net according to the negative qty's based off of Column F.

[ahuang3433]

Just wondering if anyone got any leads on this?

[ahuang3433]

I think I confused myself with the first initial request. Here is what I wanted to do.

Sheet 2 shows the inventory available at the specified location. Location in discussion is 10TE02 and 10TW01.

What I'm trying to do is that based on qty needed, we have +50 in 10TW01 vs -40 in 10TE02. Column G would show that I have the +50 available until I hit a negative at column F how many we are short. Column H will show the net balance after we supply to all of the needed qty.

I hope this explains it better.

New attachment attached to show what I'm talking about.

[kvsrinivasamurthy]

It is not clear how you get values in columns G & H.Manually show how you get those numbers in the file and also G8=50. How it is.

[ahuang3433]

It show 50 because that's the balance left over from 10TW01 location. There is 100 pcs in each location. It shows a surplus of 40 in 10TE02 location. So in order to cover the surplus in 10TE02, we used the balance of what is left over in 10TW01 to cover. It doesn't start deducting from that inventory until row 8 because that's when it goes negative for 10TE02. I hope this helps.

[ahuang3433]

I'm sorry for the confusion but here is another example that is showing what I want.

Column U shows the excess availability of the part reference sheet 2 column Z. Row 2 in sheet 1 shows 273 available but since the net inv (origin) shows -340, all 273 will be consumed which leaves -67 at the Net Inv (alt). Row 3 of column U should start with -67 because it is what row left us with. The focal point is to net the excess is the net inv origin is >=0.