Hi David

Give this a try.

Hope this helps.

Sunny

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.

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

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.   

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

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 ..

Here I have a diagram for you.

Thanks Sunny.

I tried first to see if I can  control

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.

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..

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

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!

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

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 !