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.
Twifoo hi,
i've add my original details for your updated chart. but there are some issues, can you please look. some items not in my clossing stock. but it shows as balance stock and some not allocated. please
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)))*D4
Note 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.
- Because you are now raising a new issue, I suggest you post it in a new conversation. Thereby, you will be able to obtain more suggestions, not necessarily from me.
HI
i have one more question. i added new column and give a condition by IF formula to get complete items and non complete items. and also i have added new sheet and copy closing stock details for it. i need to make a hyperlink when click on condition column cell to filter relevant data from sheet 2
- You're welcome. I'm glad we finally did it.
- every thing sorted. thank you very much for your support. appreciate that