First time visitor with a headache.
I'm just learning about Pivot Tables (P-T), and I need someones help in how to extract data.
I will try and make this simple.
Sheet1 (SHT1) has my Bill of Materials for an assembly.
Sheet2 (SHT2) has my P-T.
Sht1 lists all my P/N's in Col B.
In COL M, I need to pull the Max dollar from 3 different Vendors from my P-T, and place it in cell M12 of Sht1.
Each P/N has 4 qty's associated with it.
In otherwords, P/N 123 needs prices quoted for 50, 100, 250, 500 pcs each.
My P-T lists the P/N's on the left,
then the Qty's are listed next.
Across the top lists all my Vendors, and their pricing for each P/N they quoted.
Let's say the Vendors run from C4:AA4.
Is there a formula I can enter in M12 of SHT1, that takes the P/N in B12, goes to the P-T, matches it, and the specific qty, then pulls the MAX value from a range and places it here?
I'm plugging away trying to find the right INDEX/MATCH scenario, but I haven't hit it yet..
Thanks for your help,