Forum Discussion
Solved
Need to allocate stock
Hi, here attached my file which i need to allocate my stock. in 1st table i have order number / delivery date / items and required qty . 2nd table i have mentioned closing stock . i need to allo...
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.
Try this formula in E4, copied down rows:
=(LOOKUP(C4,K$4:L$8)>=
(SUMIFS(D$4:D$63,B$4:B$63,"<"&B4,C$4:C$63,C4)+
SUMIFS(E$3:E3,B$4:B4,B4,C$4:C4,C4)))*D4
=(LOOKUP(C4,K$4:L$8)>=
(SUMIFS(D$4:D$63,B$4:B$63,"<"&B4,C$4:C$63,C4)+
SUMIFS(E$3:E3,B$4:B4,B4,C$4:C4,C4)))*D4