getpivotdata relative reference?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Betty Bogg - 27 Sep 2007 17:21 GMT

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!

Reply to this Message

Roger Govier - 28 Sep 2007 00:30 GMT

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

Signature

Regards
Roger Govier

> 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
[quoted text clipped - 9 lines]
> way to make the copy become relative, rather than absolute? Thank in
> advance!

Reply to this Message

Betty Bogg - 02 Oct 2007 17:18 GMT

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

> Hi
>
[quoted text clipped - 17 lines]
>> way to make the copy become relative, rather than absolute? Thank in
>> advance!

Reply to this Message

Roger Govier - 02 Oct 2007 18:24 GMT

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.

Signature

Regards
Roger Govier

> Thanks Roger, that's very helpful. I was actually hoping to retain the
> functionality of the getpivotdata function (when I switch the data around
[quoted text clipped - 29 lines]
>>> way to make the copy become relative, rather than absolute? Thank in
>>> advance!

Reply to this Message