Search code examples
excelvbavlookupinventory

Excel qty IN/qty OUT formula

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?

Solution

  • 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.

    enter image description here

    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:

    enter image description here