Log In
Register
Home
December 5, 2016
Offline
Dear Sir,
In our warehouse we have suppliers deliver our parts, ideally they should quote our Purchase order, but sometimes missed, sometimes they only provide the materials and Qty delivered information in theri packing list. So, our question is how to set a formula match each quantity delivered [ assume First in First out basis against our outstanding Purchasing order balance in System relying on the only common key " Material"
Pls refer attached sample.
Thanks your help
B.Rgds
David
VIP
Trusted Members
June 25, 2016
Offline
Hi David
Give this a try.
Hope this helps.
Sunny
December 5, 2016
Offline
Thanks Sunny, but how can the formuda extend to the rest of the column F.
as delivered quantity is at random with reference to the material.
VIP
Trusted Members
June 25, 2016
Offline
Hi David
Maybe you can give as many scenario as possible in your example with the expected result (very important).
I didn't quite understand the part on "delivered quantity is at random with reference to the material".
Sunny
December 5, 2016
Offline
Sure, please refer the attached again as the material deliverd from the Vendors to our warehouse varies daily
and from their packing list [ soft copy ] we extract only the material field and quantiy but no our Purchase order number can be extracted so we manuallly allocate the quantity recevied arbitrarily allcoate to each open Purchase order until each Purchase order is duly completed. thus we want a formula to allocate QUANTITY received with respect to each respective material received.
In your previous formula appied it almost there, but I just don't know how to extend the forumula to the rest in Column F. because when I copy the formula to ther rest of the cells in F column , it didn't work out becasue the relative and absolute address of the Formula changed, and I don't know how to anchor the cells to obtain the desired allocation ie the result.
VIP
Trusted Members
June 25, 2016
Offline
Hi David
These formulas does not work by just copying them to another cell.
This is what you can do:
1) Let's say you copy the formula from cell F5 to cell F17.
2) Select cell F17 and press the F2 edit key (not the cell F2
) to edit the formula.
3) The cells that the formula refer to will be highlighted with colored boxes.
4) Just drag the highlight to the correct cells.
Hope this helps.
Sunny
December 5, 2016
Offline
Sunny, you mean drag all highlighted boxes to the designated Cells ?
But when I press Function key F2, boxes is hgihlighted , but can not drag to the Cell(s) I want ..
VIP
Trusted Members
June 25, 2016
Offline
Here I have a diagram for you.
December 5, 2016
Offline
Thanks Sunny.
I tried first to see if I can control
December 5, 2016
Offline
Thanks Sunny , I tried it seems work out , but drag and dropped actions seems a very tediuos exercise , is there other simple way
we can copy the formula, though we can manuplate the raw data.
December 5, 2016
Offline
How about if we reshuffle the data then applied the formula you illustrate ( ie can copy the formula) , a the drag and dropped action is very difficult to use if the row data become huge..
VIP
Trusted Members
June 25, 2016
Offline
Hi David
I have looked at reshuffling the data and even separating them into different sheets (may not be practical as you may have many customers/products) etc but have not been able to find a workable and practical solution so far.
Your situation require that certain cells to be absolute (locked)
Sunny
December 5, 2016
Offline
Really deep thanks Sunny you take the effort look into the case. Yes we are handling few hundreds Vendors and about hundred Customers in our Group purchase, anyway your solution do help a lot already. Thanks again!
VIP
Trusted Members
June 25, 2016
Offline
Hi David
See if this will work. It needs a helper column.
Just make sure that the 1st row is the delivered value e.g. cells D4, D16 etc. It can be 0 if there are no deliveries.
Sunny
December 5, 2016
Offline
Sunny, you are superb, armed with this creative idea ie setting the helper column, this really bring us home and can make us early to bed. We cherish that idea so much as sometimes the delivered quantity which we need to allocate by manual to the Purchase Order takes ages to complete, worse still, allocation is oftern wrong, now not only take few minutes to complete but with comfort of assurance in accuracy. Really thanks !
VIP
Trusted Members
June 25, 2016
Offline
Hi David
Thanks for your feedback and compliments
By helping you, I have also learned a thing or two. Otherwise I wouldn't have even look at such a situation.
Sunny
December 5, 2016
Offline
Allow me Sunny I modified the formula a bit. ie Put a conjunction( two condition ) in the If command to make it more precise.
VIP
Trusted Members
June 25, 2016
Offline
Hi David
You can modify to suit your needs.
Just remember that the 1st row must be the delivered value e.g. cells D4, D16 etc Otherwise the formula will fail.
I noticed that quite a number of rows (e.g. rows 43, 46, 49 etc) for the delivered values are not added in your attachment. I suggest you add them BEFORE you copy the formulas to prevent any problem.
Sunny
1 Guest(s)
