Formula referencing Pivot Table 2

[Scott02]

Good morning. This will probably be a simple question to answer, but I'm stumped at the moment. When I'm trying to create a formula in a cell and it's referencing the data on a pivot table, the formula comes up with something like
=+GETPIVOTDATA("Average of Mtd",$A$3,"Denom",0.01,"Game Type","Reel"). What I really need is for it to be =C3. I've had this problem before and asked a friend how to fix it, but it's been so long, that I can't remember where the fix was. I appreciate the help.
Scott

 

[koresnordic]

how about remving the getpivotdata and just adding "C3"? works for me



Graham

 

[AnotherHiggins]

GETPIVOTDATA is a function available in Excel versions 2002 and later. It is a way to dynamically reference values in a Pivot Table. What's the problem with having that formula in your cell? Does it not return the expected value?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

Does it not return the expected value?

it will do but you can't copy it down

To get rid of it, make sure the pivot table toolbar is visible and click in any cell within the pivot table

Click once on the "Generate GetPivotData" button on that toolbar (hover over them for the descriptions) - this is a toggle button that sets whether the getpivotdata formula is automatically generated when you reference a cell in a pivot table



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Scott02]

Thanks for the quick replies. The formula does work, but I'm using the data from a minimum of 3 and up to 8 columns from the pivot table to do additional calculations outside the table. When it started this GetData thing again, it was like losing a simple tool and gaining a troublesome one.

Geoff's solution above worked for me. Strange thing was the button you mentioned was not on my pivot table tool bar. I had to customize and add it. No idea where it went before. Thanks very much for the help! Life is good again!

Scott

 

[Gavona]

Great tip Geoff. Strangely I cannot modify my pivot table toolbar but was able to add the button to one of my other toolbars. (any ideas?)
To help others the button is found in the Data category.

Regards,

Gavin

 

[xlbo]

Sorry Gavin - no idea - I have had no issues getting it on / off the pivot table toolbar

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question