Excel Pivot table range reference

Question

Hi I’m trying to make some calculation starting from data stored in an excel pivot table.

I know there is a GetPivotData function, which is very useful, but it only allows retrieving single values. I’d like to use worksheets function to “query” my data (for example, sum some values in a column, without filtering the pivot table), but I can’t find a way to easily reference the range of the pivot table. The “structure” of the table may be assumed to be fixed, but not the data in it: the best solution I found is to write a UDF function to get the reference to the range of the table:

 Function getPvRange(firstcell As Range) As Range
    Set getPvRange = firstcell.PivotTable.TableRange1
 End Function

I can use this function in my formulas. I was wondering if there is a simple (I mean without defining dynamic range names) direct way (I mean, not using VBA), to reference the Pivot Table range?

Answer 1

If your sheet is simple, using excel Offset function is a way to get Pivot Table Range as the function you provided. for example, created a named range and assign range as

=offset($A$1,,,counta($a:$a),15)

Answer 2

If you just want to refer to cells within a pivot table as normal cells, you can disable the automatically generated GetPivotData functions. Bring up the pivot table toolbar, click add buttons, and select 'Generate GetPivotData'. You can then use the new button to toggle the automated formulas.

Or are you trying to do something a bit more complicated? If so, please could you post an example?