Can GETPIVOTDATA refer to the pivot table name?

  • Thread starter Thread starter Green Biro
  • Start date Start date
G

Green Biro

Is there any way to use the pivot table name (from the table options
dialog)?

I want to make my report more maintainable and robust ie If I could say
GETPIVOTDATA("Key","Maths Exam",.....) it would be more readable than
GETPIVOTDATA("Key",Pivot!$e$4,......)

Thanks in advance for any assistance.

GB
 
You can't refer to the pivot table by name, but you could name a cell in
the pivot table, and refer to that name.

For example, name cell E4 as Math_Top and the formula would be:
GETPIVOTDATA("Key",Math_Top,.....)
 
Thanks for that.
I decided though that I would feel better using the table name and so I
created a UDF as follows:

Function PivotLocation(argName As String)
Dim pv As PivotTable
Set pv = Worksheets("Pivots").PivotTables(argName)
PivotLocation = pv.TableRange1.Address
End Function

My cell now reads:
=GETPIVOTDATA("key",INDIRECT("Pivots!"&PivotLocation("Maths")),"score",2)
and that works OK.

The reason for my posting again is that I wandered if you could take a look
at my function. I thought that it would simply be possible for my UDF to
return a reference but no matter that I set it to return a range or even a
variant, my cell calculated as #VALUE! That's why in the end I had to
return the address property (a string) and use INDIRECT to get the
reference. Here's my non working UDF. What was I doing wrong?

Function OldPivotLocation(argName As String) as Range
Dim pv As PivotTable
Set pv = Worksheets("Pivots").PivotTables(argName)
OldPivotLocation = pv.TableRange1
End Function

Cell: =GETPIVOTDATA("key",OldPivotLocation("Maths"),"score",2) returns
#VALUE!


Thanks in advance for any insight.

GB
 
Change it to:

Set OldPivotLocation = pv.TableRange1

and it should work correctly.
 
Thanks for that. I had tried it without the '.address' but I'd forgot the
all important keyword 'Set'. Normally when I make that error, I get an
'object not set' type of error. Obviously, errors get 'masked' a bit in
UDFs.
Anyway, I'm very pleased with the result so thanks again for your help.

GB
 
Back
Top