Copy data from pivot table using relative reference
 

Hello

I have created a pivot table, and I would like to extract data from it for
further calculations (basically, summing, subtracting, etc the columns that
are in the pivot table). If I use "getpivotdata", or simply refer to the cell
with the "=" sign, excel returns the value in that cell with an absolute
reference. I need relative references, so I can copy down the whole length of
the table. If there is an easier way to do this, that would be nice as well.

Thank you

Copy data from pivot table using relative reference
 

Hi

Just amend the absolute in the formula created by GetPivotData to a
relative as far as the row is concerned
e.g. change $A$5 to $A5.
Sometimes GetPivotData will refer to a particular label, which you may
also need to change to a cell relative reference.

--
Regards

Roger Govier


"hello" wrote in message
...
Hello

I have created a pivot table, and I would like to extract data from it
for
further calculations (basically, summing, subtracting, etc the columns
that
are in the pivot table). If I use "getpivotdata", or simply refer to
the cell
with the "=" sign, excel returns the value in that cell with an
absolute
reference. I need relative references, so I can copy down the whole
length of
the table. If there is an easier way to do this, that would be nice as
well.

Thank you

Copy data from pivot table using relative reference
 

Roger

Thank you for your help. I think I did not explain fully. If I refer to a
cell in the pivot table (by simply typing =, and clicking on the cell in the
pivot table), here is the formula that excel puts in the cell. Even if I take
out the absolute reference by changing to A3, the rest of the information in
the quotation marks stays, and excel populates all cells with the value in
A3.

=GETPIVOTDATA("Corrected value",'Mar 2007 P'!$A$3,"Series
ID","ces2023610001","Year",1985,"Period","M01")

In the formula above, "Corrected value" is the series in the data area
'Mar 2007 P' is the name of the sheet that contains the pivot table
"Series ID" is the series in the column area
"ces2023610001" is one of variables in "Series ID"
"Year" is the series in the row area
1985 is one of the variables in "Year"
"Period" is another variable in the row area
"M01" is one of the variables in "Period"

If you could provide further advice I would be most grateful.

Thank you







"Roger Govier" wrote:

Hi

Just amend the absolute in the formula created by GetPivotData to a
relative as far as the row is concerned
e.g. change $A$5 to $A5.
Sometimes GetPivotData will refer to a particular label, which you may
also need to change to a cell relative reference.

--
Regards

Roger Govier


"hello" wrote in message
...
Hello

I have created a pivot table, and I would like to extract data from it
for
further calculations (basically, summing, subtracting, etc the columns
that
are in the pivot table). If I use "getpivotdata", or simply refer to
the cell
with the "=" sign, excel returns the value in that cell with an
absolute
reference. I need relative references, so I can copy down the whole
length of
the table. If there is an easier way to do this, that would be nice as
well.

Thank you

Copy data from pivot table using relative reference
 

Wait -- I think I got it. All I have to do is type =, the sheet name, and the
relative cell reference. Hope it works after I refresh all the data..

"Roger Govier" wrote:

Hi

Just amend the absolute in the formula created by GetPivotData to a
relative as far as the row is concerned
e.g. change $A$5 to $A5.
Sometimes GetPivotData will refer to a particular label, which you may
also need to change to a cell relative reference.

--
Regards

Roger Govier


"hello" wrote in message
...
Hello

I have created a pivot table, and I would like to extract data from it
for
further calculations (basically, summing, subtracting, etc the columns
that
are in the pivot table). If I use "getpivotdata", or simply refer to
the cell
with the "=" sign, excel returns the value in that cell with an
absolute
reference. I need relative references, so I can copy down the whole
length of
the table. If there is an easier way to do this, that would be nice as
well.

Thank you

Copy data from pivot table using relative reference
 

Hi

Supposing you want the Period to alter as you go down the page from Mo1
to Mo2 etc.
and supposing Mo1 is held in cell C5 of your Pivot table, change the
formula to

=GETPIVOTDATA("Corrected value",'Mar 2007 P'!$A$3,"Series
ID","ces2023610001","Year",1985,"Period",C5&"")

Whenever you refer to a cell in the GetPivotData function, it needs a
null character "" either prepended or appended to the cell reference

If the Year is to vary as well, then you would need something like
"year", B5&"",

--
Regards

Roger Govier


"hello" wrote in message
...
Roger

Thank you for your help. I think I did not explain fully. If I refer
to a
cell in the pivot table (by simply typing =, and clicking on the cell
in the
pivot table), here is the formula that excel puts in the cell. Even if
I take
out the absolute reference by changing to A3, the rest of the
information in
the quotation marks stays, and excel populates all cells with the
value in
A3.

=GETPIVOTDATA("Corrected value",'Mar 2007 P'!$A$3,"Series
ID","ces2023610001","Year",1985,"Period","M01")

In the formula above, "Corrected value" is the series in the data area
'Mar 2007 P' is the name of the sheet that contains the pivot table
"Series ID" is the series in the column area
"ces2023610001" is one of variables in "Series ID"
"Year" is the series in the row area
1985 is one of the variables in "Year"
"Period" is another variable in the row area
"M01" is one of the variables in "Period"

If you could provide further advice I would be most grateful.

Thank you







"Roger Govier" wrote:

Hi

Just amend the absolute in the formula created by GetPivotData to a
relative as far as the row is concerned
e.g. change $A$5 to $A5.
Sometimes GetPivotData will refer to a particular label, which you
may
also need to change to a cell relative reference.

--
Regards

Roger Govier


"hello" wrote in message
...
Hello

I have created a pivot table, and I would like to extract data from
it
for
further calculations (basically, summing, subtracting, etc the
columns
that
are in the pivot table). If I use "getpivotdata", or simply refer
to
the cell
with the "=" sign, excel returns the value in that cell with an
absolute
reference. I need relative references, so I can copy down the whole
length of
the table. If there is an easier way to do this, that would be nice
as
well.

Thank you

Copy data from pivot table using relative reference
 

There are instructions here for adding the Generate GetPivotData button
to a toolbar, and toggling the feature on and off:

http://www.contextures.com/xlPivot06.html


hello wrote:
Wait -- I think I got it. All I have to do is type =, the sheet name, and the
relative cell reference. Hope it works after I refresh all the data..

"Roger Govier" wrote:


Hi

Just amend the absolute in the formula created by GetPivotData to a
relative as far as the row is concerned
e.g. change $A$5 to $A5.
Sometimes GetPivotData will refer to a particular label, which you may
also need to change to a cell relative reference.

--
Regards

Roger Govier


"hello" wrote in message
...

Hello

I have created a pivot table, and I would like to extract data from it
for
further calculations (basically, summing, subtracting, etc the columns
that
are in the pivot table). If I use "getpivotdata", or simply refer to
the cell
with the "=" sign, excel returns the value in that cell with an
absolute
reference. I need relative references, so I can copy down the whole
length of
the table. If there is an easier way to do this, that would be nice as
well.

Thank you





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html