Running totals on Purchase Order tracking

[klat7292]

I have created a workbook template where I have a 'master' page for an Order
Acknowledgment/Confirmation for a Purchase Order for items that are purchased
from one vendor and are waiting to be received. On separate sheets, I have
created a similar template to receive the items purchased on multiple
Purchase Orders for that one original Purchase Order due to backorders. This
is simply for tracking everything on the Original Purchase to insure that all
inventory is accounted for without all the confusion and paper trail. What I
am wanting to do is have a running total of backordered items for each item
ordered on the mater page as new purchase orders for backorderes are entered
for that particular Original Purchase Order.

If someone could let me know how this is done, I would greatly appreciate it
and it would save me a lot of HEADACHE!! I think that I am on the right track
and I am close-if all else fails, I could always enter the info manually, but
I think that might open the door for more error...I know that it can be done!!

Thanks in advance for the answer!!

 

[JLatham]

Look into using either VLOOKUP() or SUMPRODUCT() to come up with the total of
received items on multiple sheets. VLOOKUP() should work if the original
item is only referenced once on any given sheet, but if you have same product
item referenced more than once on the receiving records sheets, then you'd
need to use SUMPRODUCT() to roll them all up.

I'll try to give quick, short example. Lets say on the Master sheet you
have an item that has the unique item number (stock number, order line item
number, what ever) in column A2 and the total quantity is in D2.

Over in a sheet we'll call Status, you'd start with the original values via
formula, so in a row (we'll use different row to help keep things separated)
you put a couple of formulas:
In Status!A4 we put =Master!A2
in Status!B4 we put =Master!D2

The receiving sheets have the same reference to the stock/order/ID number in
column A beginning at row 2 and continuing down the sheet, with the number of
items received in column C. These sheets are named like RECV1, RECV2, etc.

Back to our Status sheet, in a column to roll up all quantities of the item
that have been recorded as received, you'd put a formula like this (using
SUMPRODUCT() since it will work under both circumstances) into cell C4
=SUMPRODUCT(--(RECV1!A1:A65536=STATUS!A4),--(RECV1!C1:C65536)) +
SUMPRODUCT(--(RECV2!A1:A65536=STATUS!A4),--(RECV2!C1:C65536))
that will give you a total of all of the particular items shown as received
on sheets RECV1 and RECV2, so if we put this formula into STATUS!D4
=C4-B4
we get the number still on back order.

You don't have to reference all the way down to row 65536 as I've done, you
can put the upper row limit at a known row number that includes all entries
on the RECV# sheets to make it more efficient, but by using 65536, you don't
have to adjust the formulas at all.

Hope this helps some.