I was told that this might be a better place to post this question than Microsoft Answers for Office.

I have a Table in which I need to use a GetPivotData function.  I need to use a cell reference in that GetPivotData function.  I have done this before with no problem in a normal cell range, but it seems like the syntax when using a Table screws things up.  Can anyone help me out here?

The GetPivotTable function would normally look like this if not in a Table.

=GETPIVOTDATA("[Measures].[Total Blocked Dollars]",'Sheet2'!$A$4,"[Dim Prod Ctrl No].[By Prod Ctrl No]","[Dim Prod Ctrl No].[By Prod Ctrl No].&[18418]")

That formula sits in a column called "Block" in my Table.  The 18418 sits in a column called "ID" in my table.  So what I have tried is:

=GETPIVOTDATA("[Measures].[Total Blocked Dollars]",'--Report Blocks--'!$A$4,"[Dim Prod Ctrl No].[By Prod Ctrl No]",concatenate("[Dim Prod Ctrl No].[By Prod Ctrl No].&[",[@[ID]],"]"))

I have tried a few combinations like this but I can't seem to use a cell reference from a table (which requires the [@[field]] syntax) to work with the GetPivotData.

Any ideas?

Thanks!

• Edited by mateoc15 Thursday, May 17, 2012 3:23 PM Error made

GETPIVOTDATA returns data from a Pivot Table. If you want to return data from a Table using some criteria you can use something like

=SUMIFS(Table1[Dollars],Table1[Prod No],F5)

That's not the issue.  The GETPIVOTDATA function is actually IN a table.  The arguments for the GETPIVOTDATA function need to reference a cell/column in the table, and it's only PART of the argument that needs to be replaced.  In the example below the 18418 needs to be replaced with the data in the same row of the table in a column named ID.

=GETPIVOTDATA("[Measures].[Total Blocked Dollars]",'Sheet2'!$A$4,"[Dim Prod Ctrl No].[By Prod Ctrl No]","[Dim Prod Ctrl No].[By Prod Ctrl No].&[18418]")

Does that clarify any?

Thanks for the response!

check this link this may help you

http://www.mrexcel.com/getpivotdata.html

• Marked as answer by mateoc15 Friday, May 18, 2012 2:23 PM

Hmm, not my favorite but it definitely works.  The problem is that you have to define a static range such as B2:C1000 rather than giving the name/location of a pivot table.  If my data changes then I manually have to change that to B2:C1001 (or whatever the case may be).  To cheaply get around that I just made my formula use the range B2:C50000, a number I'm sure (I HOPE!) I'll never reach.

Thanks Hukka.

• Edited by mateoc15 Friday, May 18, 2012 2:24 PM

I did not get any answers in the link provided below. Did you figure out a solution for this issue?