How to Use pivot tables in a calculation


Mervyn Thomas

I have some data in a pivot table which I want to use in a calculation but
when I try to key off a cell in the table the new cell is a calculation
....GetPivotTable....... and it is impossible to drag this cell down to
other cells that correspond to other cells in the pivot table.
Am I explaining myself??
Or: How do I refer to the contents of a cell in a pivot table?





Roger Govier

Hi Mervyn

You can replace the direct row names in the GetPivotData formula, with the
cell reference that contains the name.
Then, as you copy down, the formula adjusts to suit the row name.
The same applies to columns.

Take a look at the help on this function at

It just takes a little while to get used to tweaking the GetPivotData
function, but it is far better than using direct cell references, as there
is no guarantee that the references will remain th same after additions to
the source data and a Refresh.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question