GetPivotData Help

[Jim_Jammy]

I have a pivot table which I reference to convert currency data int
GBP. I have various categories that mean I have a table that i
several thousand rows by the number of currencies as columns.

I WANT to use the GETPIVOTDATA function in all the cells in th
conversion table rather than referencing them by cell e.g “A4”. Thi
is important as I need to use the table again and there could be ne
rows once the pivot table is refreshed.

What I can’t work out is how to copy a cell with the GETPIVOTDAT
formula across the remaining cells in the table – I don’t want the hav
to manually create thousands of cells. Using cell references will mea
I pick up the wrong data in a summary table when new rows are added a
the conversion table won’t expand as the pivot table does.

Can anyone help

 

[Debra Dalgleish]

In the GETPIVOTDATA formula, you can refer to heading cells in your
summary table. For example, in Excel 2002, with headings in cells H4:J4
and G5:G9:

=GETPIVOTDATA("Units",$A$5,"Region",H$4,"Rep",$G5)

The field names (Region, Rep) are typed in the formula, but the item
names come from the summary table headings (H$4, $G5).

Copy the formula across to column H, and down to row 9.

If you're using an earlier version of Excel, the arguments for
GETPIVOTDATA are different (check Excel Help for examples).

 

[Jim_Jammy]

Thanks Debra,

This helped to put me on the right path. Your solution does work
although I have had to create another data table to make it fit m
requirements.

I have 3 row categories to break up values under a single code (Th
first category) Your solution is fine if there is only 1 row headin
but the pivot table leaves blank cells where you have multipl
variables in the 2nd and 3rd row categories.

This means that the cell referenced values in your solution pick u
blanks and return errors rather than values. I have therefore create
a second table that populates all cells in the row categories 1-
(using if statements) so that the cell refs can find the require
variables to return a value - This will also capture changes when
re-run the pivot.

I'm sure you knew this anyway but thought I'd post back my comments i
case they are of use to anyone.

Thanks agai