using pivot table data outside of the table - office 2003

[Grypht]

Hi,
When I try to use data outside the pivot table, instead of having the number of the cell (like in Excel 2000) I get "=GETPIVOTDATA("CDN",$D$1,"#eqp",1,"Category","Characterization","Name equipement","XRF")" // I get this by clicking the cell I want.

and if I want to copy the formula to other cells, it always use the same cell instead of moving down and using the cell value that is under.

Sorry If I am not clear, a user submited this bug to me, and I just started working with pivot tables because of him.

Any idea why I am not getting the cell number?
Francois

 

[youngcougar]

The formula returns the resultant data that the cell holds, not the actual cell number. When you copy the formula to other cells, $D$1 indicates an absolute referance, which means the cell number will not increment. In order to increment the cell number, remove the '$' from $D$1. If you want the cell number, try using the index() function wrapped around the = getpivotdata().

 

[Grypht]

That formula is entered automatically by Microsoft Excel, and I don't understand why they would give me that formula instead of the cell value.

all we do is, put "=" in the cell then click the cell we want.

In Office 2000 it would give us the cell selected, and not as an absolute referance.

We found that if we type the cell number manually, it works, but doing all that work manually will take a lot more time then before.

Isn't there an option (I haven't been able to find anything) that turns back the formula into the cell number?

Francois

 

[xlbo]

Indeed there is.
On the pivot table toolbar, there should be a small arrow in the bottom right
Click it and choose "Add or Remove Buttons"
Choose "Pivottable" and navigate down to the "Generate GetPivotData" option and select it

Once this is on your pivottable toolbar, clicking it once will deativate the effect you are seeing. Clicking it again will turn it back on

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Grypht]

It worked!
Thank you very much Geoff

Francois

 

[xlbo]

It's actually very useful to be able to turn it on as it means that you can dynamically refer to pivot tables that may change in size - it would be nice, however, if it wasn't turned on by default !! That was the 1st thing I changed when we moved onto XP !

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question