Help with GETPIVOTDATA drag down formula

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
351
Office Version
  1. 2019
Platform
  1. Windows
Hello,

I'm trying to use GETPIVOTDATA to fill in the "GS$" column shown below, with info pulled from the Pivot Table next to it (but on a different sheet in my WB). I'm running into the common problem where I grab the 1st cell no problem, but then when I drag the cell with my GETPIVOTDATA formula down, it continues grabbing the sales number for the first Org # (Org 237), instead of next grabbing the info for Org 839, etc.

=GETPIVOTDATA("[Measures].[Sum of Line Item Total]",'Yest GS'!$B$3,"[Monthly Orders Reports].[Date]","[Monthly Orders Reports].[Date].&[2021-03-27T00:00:00]","[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[237]")
***"Monthy Orders Reports" by the way is just the name of the data folder where all of my data is.
I tried replacing the last part :
"[Monthly Orders Reports].[Org #].&[237]"
with this:
E6 (E6 is the cell below which contains the number "237"
but it's giving me a #REF error.

Could someone please help me figure this out?

Green/white table is on "Proj" tab: Black/white Pivot Table is on "Yest GS" tab:
1616917460051.png
1616917536462.png
 
"[Monthly Orders Reports].[Org #].&["&[@[Org '#]]&"]"
 
Upvote 0
Solution

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Mart37, that did it! I'm trying to understand what yuou did there, but I'm not quite following. I'm just happy it works, thank you so much!
 
Upvote 0
The Pivot Table based on a query needs his input in string format. So I use a combination of the old formula with on the spot of the value a link with the table cell.
This the first part: "[Monthly Orders Reports].[Org #].&["
This the second part: [@[Org '#]]
This the third part: "]"
The & concatenate the parts.
 
Upvote 0
Mart37,
On one of the other columns in this same table, why does this work:
=GETPIVOTDATA("[Measures].[Sum of Quantity 2]",'R&E Yest'!$B$5,"[Order Returns Report].[Org #]","[Order Returns Report].[Org #].&[839]")
But when I add your fix to this column (so that I can drag it down and it will update for each different Org #), I get a reference error?
=GETPIVOTDATA("[Measures].[Sum of Quantity 2]",'R&E Yest'!$B$5,"[Order Returns Report].[Org #]","[Monthly Orders Reports].[Org #].&["&[@[Org '#]]&"]")
 
Last edited:
Upvote 0
Mart37,
On one of the other columns in this same table, why does this work:
=GETPIVOTDATA("[Measures].[Sum of Quantity 2]",'R&E Yest'!$B$5,"[Order Returns Report].[Org #]","[Order Returns Report].[Org #].&[839]")
But when I add your fix to this column (so that I can drag it down and it will update for each different Org #), I get a reference error?
=GETPIVOTDATA("[Measures].[Sum of Quantity 2]",'R&E Yest'!$B$5,"[Order Returns Report].[Org #]","[Monthly Orders Reports].[Org #].&["&[@[Org '#]]&"]")
I pasted in the wrong thing. I meant to say, why is this one not working?
=GETPIVOTDATA("[Measures].[Sum of Quantity 2]",'R&E Yest'!$B$5,"[Order Returns Report].[Org #]","[Order Returns Reports].[Org #].&[" & [@[Org '#]] & "]")
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

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
Back
Top