getpivotdata relative reference?

[Betty Bogg]

I'm making several spreadsheet showing month cashflow data that rolls up
from pivot table on another sheet. I've been selecting the cell I want the
data to go to, entering "+", then selecting the pivot table cell I want to
get the data from. This works great and pops the getpivotdata function into
the cell I want it in. BUT, when I copy the cell with the getpivotdata
function in it over to subsequent months it copies the exact same reference,
and I have to go in and change it manually, or individually add the
getpivotdata function to the subseqent cells one at a time. is there any
way to make the copy become relative, rather than absolute? Thank in
advance!

 

[Roger Govier]

Hi

Amend your GetPivotData reference, to the cell reference in column A as a
relative reference.
For more help take a look at Debra Dalgleish's site.
http://www.contextures.com/xlPivot06.html

 

[Betty Bogg]

Thanks Roger, that's very helpful. I was actually hoping to retain the
functionality of the getpivotdata function (when I switch the data around or
sort it differently and switch it back). Sometimes I will sort the data in
a way in which the row/column reference would dissappear, and then I'd have
to re-enter the data because the #REF loses the formula. getpivotdata
reinstates the result when i sort back, regardless of the cell location.
Any other way to do a relative copy without moving away from getpivotdata?

Betty

 

[Roger Govier]

Hi Betty

I would have 2 PT's (at least), based upon the same source data.
The one you want to use for your cashflow, with the GetPivotData set up as
described previously, and this PT would not be altered in any way. I usually
put mine on a hidden sheet (away from meddling fingers<bg>)

The second PT would be visible, and would be the one which is used to
"Pivot" the data and play about with the different views.