Log In
Register
Home
February 2, 2013
Offline
Hi All,
I need to update Arrived qty (c10:c12) in (e3:e6) based on S.L (b column) and delivery date (c3:c6), there is possible to auto update formula.
regards/Saliha
Trusted Members
February 13, 2021
Offline
Hi Saliha!
I came up with a SUMIFS nested inside an IF; it was fun! You will need to allow for growth, or, better yet, make it a table so the formula will automatically grow with the tables. Hopefully, this helps you!
February 2, 2013
Offline
Hi Jessica Stewart
Thanks for your help but there is one more step is missing delivery date, bcoz we need to update qty date wise with S.L.
- one more i noticed if i remove i10 arrived qty 225 result displaying in k3 arrived qty is -150 in negative,
- actually i12 qty (100) should be update in k3 cell and balance qty will be 150
- and k6 arrived qty will be zero.
Saliha
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
I would recommend to change the set-up a bit. Have one table with three columns for the quantities (OrderedQTY, ReceivedQTY and AllocatedQTY). Then you can fairly easily calculate the allocation of received quantities. Note that I didn't consider the possibility that you would receive more than ordered, but that shouldn't be all that difficult either.
Please see if the attached solution could work for you.
Trusted Members
February 13, 2021
Offline
I see what you did there, Riny; much more simple and cleaner! I like it.
February 2, 2013
Offline
Hi Riny / Jessica
Thanks for you help, i have achieve 80% of my work based on your allocated formula option, possible to add delivery date wise, which i was message on first.
qty should be update first delivery date wise
I need to update Arrived qty (c10:c12) in (e3:e6) based on S.L (b column) and delivery date (c3:c6), there is possible to auto update formula.
regards/Saliha
February 2, 2013
Offline
Hi Riny
i have updated your formula working file but i need qty updation based on first of date condition
Saliha
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Not sure I follow and you didn't put all transactions in one table with separate columns for Qty Ordered and Qty Received. Then you can sort all transactions by date in ascending order, and apply the formula I used in my previous post.
1 Guest(s)
