Pivot Table References

In 2000, I think you could refer to a Grand Total in a
Pivot Table in another formula, copy it down, and have it
be a relative reference. Now, it seems to automatically
become absolute, and we haven't found a good way to change
it.

Example: You have a PT with a Grand Total in the last
column. You want to calculate the percentage of that row
total to the Grand Total of that column. So, you tell it
to divide the Row Grand Total with the Extended Grand
Total. If you copy that formula down, it will continue to
refer to the first row. The formula looks like this:

=GETPIVOTDATA("ExtendedPrice",$A$3,"Product","Bivy
tent")/GETPIVOTDATA("ExtendedPrice",$A$3)

Any help is appreciated!

You can choose ToolsCustomize, and from the Data category on the
Commands tab, add the Generate GetPivotData command to one of your
toolbars. Use this button to toggle the feature on and off.

Or, you can type an equal sign, and type the cell reference, instead of
clicking on the cell to reference it.


Bufo wrote:
In 2000, I think you could refer to a Grand Total in a
Pivot Table in another formula, copy it down, and have it
be a relative reference. Now, it seems to automatically
become absolute, and we haven't found a good way to change
it.

Example: You have a PT with a Grand Total in the last
column. You want to calculate the percentage of that row
total to the Grand Total of that column. So, you tell it
to divide the Row Grand Total with the Extended Grand
Total. If you copy that formula down, it will continue to
refer to the first row. The formula looks like this:

=GETPIVOTDATA("ExtendedPrice",$A$3,"Product","Bivy
tent")/GETPIVOTDATA("ExtendedPrice",$A$3)

Any help is appreciated!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks, Debra!

We did figure out the typing in thing, but we didn't know
about the toolbar button.