Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy GetPivotData Outside Report | Excel Worksheet Functions | |||
Copy Function | Excel Discussion (Misc queries) | |||
How do I turn OFF the GETPIVOTDATA function? | Excel Discussion (Misc queries) | |||
Using IF function to copy. | Excel Worksheet Functions | |||
GETPIVOTDATA function | Excel Worksheet Functions |