I need help with a formula calculating qty in and out based on need date where oldest date pulls first if the PN matches an item on the inventory list.
I have a master list of total on-hand inventory.
PN | Total QTY on-Hand |
---|---|
aaa | 10 |
bbb | 15 |
ccc | 0 |
I need to compare the total on-hand inventory list with individual order lines, but I cannot figure out how to get the remaining balance to be the starting balance (QTY OH column) of the next line. Below is what I would like it to look like.
Date Due | PN | QTY OH | QTY Demand | QTY Balance |
---|---|---|---|---|
1/15/2023 | aaa | 10 | 2 | 8 |
1/17/2023 | aaa | 8 | 3 | 5 |
1/20/2023 | aaa | 5 | 4 | 1 |
1/19/2023 | bbb | 15 | 11 | 4 |
1/25/2023 | bbb | 4 | 6 | -2 |
1/30/2023 | bbb | -2 | 3 | -5 |
1/11/2023 | ccc | 0 | 8 | -8 |
1/16/2023 | ccc | -8 | 7 | -15 |
1/21/2023 | ccc | -15 | 4 | -19 |
I have tried helper columns with vLookup (can't get away from circular references) and multiple IF/AND/OR statements. I can do SumProduct and SumIf, but I need to know the qty by the individual demand, not just the total. Is this something I can do with a formula or I need to move to VBA?
Subtract the sum of the qty demand(SUMIFS) to that point from the original qty on hand.
=VLOOKUP(B2,H:I,2,0)-SUMIFS($D$1:D1,$B$1:B1,B2)
Note what is and what is not absolute referencing. As the formula is dragged down it will only refer to the values above.
And, as you can see, since we are using conditional formula it does not matter that it is sorted by date instead of by PN and Date: