Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Elaine
 
Posts: n/a
Default Copy GetPivotData Function

I want to copy the GetPivotDataFunction from outside the Report.
When I create the GetPivotData function with the Generate GetPivotData
feature turned on and I trend/copy the function it does not update the cell
reference from the PivotTable Report.

What am I doing wrong?

Thank you again for your help.

Elaine


--
Thank you... Elaine
  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

Elaine,

You are doing nothing wrong, it's just that your expectations exceed the
capabilities of that function.

When you use the GETPIVOTDATA function, you can create the initial function
call by typing an equal sign, then selecting a cell within your pivot table.
It will create a function like

=GETPIVOTDATA(Data_Field,Pivot_Table,Field1,Item1)

but those get filled out with constants
=GETPIVOTDATA("Field 1",$A$3,"Unit 1","Fred")

You can change any of the constants to a cell reference

=GETPIVOTDATA("Field 1",$A$3,A4,B4)

and then when you copy it, it will update properly.

Note though, that when you hide or show detail, causing the pivot table to
expand or contract, the references that you use will not properly update.
To get around that, it is better to create a table of key values outside the
pivot table that the GETPIVOTDATA function can reference.

HTH,
Bernie
MS Excel MVP


"Elaine" wrote in message
...
I want to copy the GetPivotDataFunction from outside the Report.
When I create the GetPivotData function with the Generate GetPivotData
feature turned on and I trend/copy the function it does not update the

cell
reference from the PivotTable Report.

What am I doing wrong?

Thank you again for your help.

Elaine


--
Thank you... Elaine



  #3   Report Post  
Elaine
 
Posts: n/a
Default

Thank you so much for your help, Bernie. You cleared up my confusion.

Elaine

"Bernie Deitrick" wrote:

Elaine,

You are doing nothing wrong, it's just that your expectations exceed the
capabilities of that function.

When you use the GETPIVOTDATA function, you can create the initial function
call by typing an equal sign, then selecting a cell within your pivot table.
It will create a function like

=GETPIVOTDATA(Data_Field,Pivot_Table,Field1,Item1)

but those get filled out with constants
=GETPIVOTDATA("Field 1",$A$3,"Unit 1","Fred")

You can change any of the constants to a cell reference

=GETPIVOTDATA("Field 1",$A$3,A4,B4)

and then when you copy it, it will update properly.

Note though, that when you hide or show detail, causing the pivot table to
expand or contract, the references that you use will not properly update.
To get around that, it is better to create a table of key values outside the
pivot table that the GETPIVOTDATA function can reference.

HTH,
Bernie
MS Excel MVP


"Elaine" wrote in message
...
I want to copy the GetPivotDataFunction from outside the Report.
When I create the GetPivotData function with the Generate GetPivotData
feature turned on and I trend/copy the function it does not update the

cell
reference from the PivotTable Report.

What am I doing wrong?

Thank you again for your help.

Elaine


--
Thank you... Elaine




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy GetPivotData Outside Report Elaine Excel Worksheet Functions 3 April 11th 05 03:35 AM
Copy Function Genie Bohn Excel Discussion (Misc queries) 0 March 23rd 05 01:28 AM
How do I turn OFF the GETPIVOTDATA function? Andrew at Uni of Technology Sydney Excel Discussion (Misc queries) 2 March 1st 05 12:59 AM
Using IF function to copy. [email protected] Excel Worksheet Functions 1 January 17th 05 05:21 PM
GETPIVOTDATA function SRiley Excel Worksheet Functions 2 December 31st 04 07:15 PM


All times are GMT +1. The time now is 05:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"