Pivot tables after Excel 97

[barbara.lewy]

I do a lot of work with pivot tables. Excel versions up to and
including Excel 97 allowed me to copy various formulas using pivot
table data the same way one ordinarily copies formulas; the cell
addresses that were recorded by clicking on pivot table cells were
relative rather than fixed. The new "get pivot data" functions which
are automatically generated generate formulas which will not adjust as
they are copied. This is a real problem for the kind of work I do. I
know that I can get around this by "killing" the pivot table, i.e.
doing copy paste special values, but often this is not something that
I want to do. Does anyone have any other suggestions? Many thanks

 

[Herbert Seidenberg]

I give the values of the Pivot Table a defined name,
say ArrayPT, then refer to it with INDEX().
The second column of a PT would then be this array formula:
=INDEX(ArrayPT,,2)

 

[Debra Dalgleish]

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

When it's turned off, you will create a normal reference when you click
on a cell in the pivot table.