Dynamic list based on multiple factors

ccfam04

New Member
Joined
Jan 20, 2022
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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!


Capture.PNG
 

Attachments

  • Capture.PNG
    Capture.PNG
    29.7 KB · Views: 6

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
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

New Member
Joined
Jan 20, 2022
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
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
EFGHIJKL
8Order=VERSCHUIVING(Blad1!$B$2;;;AANTALARG(Blad1!$B$2:$B$1000);3)
9Shipment=VERSCHUIVING(Blad1!$F$2;1;;AANTALARG(Blad1!$F$2:$F$1000);3)
10Shipment_PN=VERSCHUIVING(Shipment;;;;1)
11Shipment_PO=VERSCHUIVING(Shipment;;1;;1)
12Shipment_QTY=VERSCHUIVING(Shipment;;2;;1)
Blad2


CCFAM04.xlsx
ABCDEFGHIJKLMNOPQR
1order logshipment log700Som van QTY ORDERED
2PNPOQTY ORDEREDPNPOQTY ShippedPNPOTotaalShipmentBalance
31A1001A501C2000200
42A1002A1001A1007525
53A1002b1001B2000200
64A1001a25--
71B2002b502A1001000
82B2003a1002B20015050
93B2004a75--
101C2003b2003C3000300
113C3003A1001000
123B2002000
13--
144A1007525
15--
16--
17--
18--
Blad1
Cell Formulas
RangeFormula
I1I1=SUM(H:H)
O3:O18O3=IF(COUNTA(K3:L3)=2,SUMPRODUCT((Shipment_PN=K3)*(Shipment_PO=L3)*Shipment_QTY),"-")
P3:P18P3=IF(COUNTA(K3:L3)=2,M3-O3,"-")


example file excel
 

Forum statistics

Threads
1,176,085
Messages
5,901,298
Members
434,886
Latest member
qazibelal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top