Dynamic list based on multiple factors

[ccfam04]

Hello. I am new to this forum and looking for some help! I have tried searching through this and other forums and have not been able to find an answer. Hoping somebody can help!

I have a much larger set of data but created the below example to try and simplify. In essence we have a list of orders by entered by Part Number (PN), Purchase Order (PO) and Quantity (Qty). We also have a list of shipments against these open PO's that are entered as product is shipped.

What we are looking for in an output table is a list of PN's by PO to show the qty remaining on each PN for each PO. I do not need a stand alone formula, I would be fine if it required multiple tables or formulas to accomplish the task. We just want the list to update as orders or shipments are entered onto the main lists.

If it helps, the list of PN's is pre-established.

Thank you in advance for any help!

 

[BSALV]

Still excel 2010 ?
Then i 'd start up your automatic populated output table as a pivottable with "order log" as source and just aside it, a sumproduct-formula with the PN & PO look in shipment log ...

 

[ccfam04]

Thank you for the quick reply. Would going to a newer version of Excel help the situation? This is for a small business use and the 2010 version was simply what was being used. Aside from that do you have any further advice on the "sum product-formula" you mentioned and how that would tie to the pivot table? Thanks again.

 

[BSALV]

One remark, a pivottable, you have to refresh it manually (or with a macro) !!!
Is this something in the good direction ?

5 defined names for ranges, so that the formulas are less complicate. (sorry in dutch, but in the attachment you can find the real ones

CCFAM04.xlsx
	E	F	G	H	I	J	K	L
8	Order	=VERSCHUIVING(Blad1!$B$2;;;AANTALARG(Blad1!$B$2:$B$1000);3)
9	Shipment	=VERSCHUIVING(Blad1!$F$2;1;;AANTALARG(Blad1!$F$2:$F$1000);3)
10	Shipment_PN	=VERSCHUIVING(Shipment;;;;1)
11	Shipment_PO	=VERSCHUIVING(Shipment;;1;;1)
12	Shipment_QTY	=VERSCHUIVING(Shipment;;2;;1)
Blad2

CCFAM04.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		order log				shipment log			700		Som van QTY ORDERED
2		PN	PO	QTY ORDERED		PN	PO	QTY Shipped			PN	PO	Totaal		Shipment	Balance
3		1	A	100		1	A	50			1	C	200		0	200
4		2	A	100		2	A	100			1	A	100		75	25
5		3	A	100		2	b	100			1	B	200		0	200
6		4	A	100		1	a	25							-	-
7		1	B	200		2	b	50			2	A	100		100	0
8		2	B	200		3	a	100			2	B	200		150	50
9		3	B	200		4	a	75							-	-
10		1	C	200		3	b	200			3	C	300		0	300
11		3	C	300							3	A	100		100	0
12											3	B	200		200	0
13															-	-
14											4	A	100		75	25
15															-	-
16															-	-
17															-	-
18															-	-
Blad1

Cell Formulas
Range	Formula
I1	I1	=SUM(H:H)
O3:O18	O3	=IF(COUNTA(K3:L3)=2,SUMPRODUCT((Shipment_PN=K3)*(Shipment_PO=L3)*Shipment_QTY),"-")
P3:P18	P3	=IF(COUNTA(K3:L3)=2,M3-O3,"-")

example file excel