Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running totals on Purchase Order tracking
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!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running totals on Purchase Order tracking
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. "klat7292" wrote: 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Project Purchase & budget tracking tool | Excel Worksheet Functions | |||
Purchase order... | Excel Worksheet Functions | |||
Purchase order | Excel Discussion (Misc queries) | |||
purchase order counter in excel purchase order template | Excel Worksheet Functions | |||
I want a purchase order that includes page number (if to be order. | New Users to Excel |