December 5, 2016
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
VIP
Trusted Members
June 25, 2016
December 5, 2016
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
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
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
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
VIP
Trusted Members
June 25, 2016
December 5, 2016
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
VIP
Trusted Members
June 25, 2016
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)