Forum Discussion
Need to allocate stock
In the attached file, I modified the formula in E4, copied down rows, to this:
=(IFNA(VLOOKUP(C4,K$4:L$1749,2,0),0)>=
(SUMIFS(D$4:D$822,B$4:B$822,"<"&B4,C$4:C$822,C4)+
SUMIFS(D$4:D4,B$4:B4,B4,C$4:C4,C4)))*D4Note that the foregoing formula also returns 0 for ordered items without available quantity in the closing stock. In the previous version, it was assumed that all ordered items are included in the closing stock but the quantities thereof may be insufficient to fulfill that quantity to be delivered on a given date.
its working 80%. but there are some issues. as a example when we filter product A , after your formula its allocated 38450. but only we have 35700. product 2 and 5 also the same. product 3 not allocated total qty. product 4 also the same. can you please help for this
In the attached file, I modified the formula in E4, copied down rows, to this:
=(LOOKUP(C4,K$4:L$8)>=
(SUMIFS(D$4:D$63,B$4:B$63,"<"&B4,C$4:C$63,C4)+
SUMIFS(D$4:D4,B$4:B4,B4,C$4:C4,C4)))*D4Note that the formula returns 0, if the closing stock is insufficient to fulfill the delivery of the required quantity of products on a given date. Stated differently, the cumulative quantity of the product as at a given date cannot exceed the closing stock of such product.
- thank you Twifoo its working calm. is there any way to allocate that balance stock for next delivery. i mean which shows as "0" but can add some qty for next delivery
- The available quantity remains 0 until such time that the quantity of closing stock becomes sufficient to fulfill the required quantity of the product on a given date. This is in accordance with the law that entitles the buyer to rescind the sales contract if the quantity delivered is less than the quantity ordered.