I'm trying to reference pivot table cells in another worksheet; a direct cell
reference doesn't work because the data is ever-changing. I figured out the
formula if I'm in the same tab, but not from a secondary worksheet.

The same-tab formula is: =GETPIVOTDATA("Budget",$A$4,"Office","ATL")

How do I re-write that to work from another worksheet? I have many others
to do, but I need the basic structure to follow.

Thanks for your help.

You can include the sheet name with the pivot table reference, e.g.:

=GETPIVOTDATA("Budget",'Pivot Table Sheet'!$A$4,"Office","ATL")

king60611 wrote:
I'm trying to reference pivot table cells in another worksheet; a direct cell
reference doesn't work because the data is ever-changing. I figured out the
formula if I'm in the same tab, but not from a secondary worksheet.

The same-tab formula is: =GETPIVOTDATA("Budget",$A$4,"Office","ATL")

How do I re-write that to work from another worksheet? I have many others
to do, but I need the basic structure to follow.

Thanks for your help.


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Hi,

No need to figure anything out, let Excel do the work for you. Build the
formula from the second sheet, by pointing and clicking in the 1st sheets
pivot table.

This works if the GETPIVOTDATA feature is on:

In 2003 you must add the toolbar for this feature by choosing View,
Toolbars, Customize, Commands Tab, Data Category, and hunt down the
GETPIVOTDATA button and drag it out to any toolbar. If its turned on Excel
will build your formula.

In 2007, while in the pivot table choose PivotTable Tools, Options, Options,
Generate GETPIVOTDATA.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"king60611" wrote:

I'm trying to reference pivot table cells in another worksheet; a direct cell
reference doesn't work because the data is ever-changing. I figured out the
formula if I'm in the same tab, but not from a secondary worksheet.

The same-tab formula is: =GETPIVOTDATA("Budget",$A$4,"Office","ATL")

How do I re-write that to work from another worksheet? I have many others
to do, but I need the basic structure to follow.

Thanks for your help.

Thanks to both of you...both solutions work!

"Shane Devenshire" wrote:

Hi,

No need to figure anything out, let Excel do the work for you. Build the
formula from the second sheet, by pointing and clicking in the 1st sheets
pivot table.

This works if the GETPIVOTDATA feature is on:

In 2003 you must add the toolbar for this feature by choosing View,
Toolbars, Customize, Commands Tab, Data Category, and hunt down the
GETPIVOTDATA button and drag it out to any toolbar. If its turned on Excel
will build your formula.

In 2007, while in the pivot table choose PivotTable Tools, Options, Options,
Generate GETPIVOTDATA.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"king60611" wrote:

I'm trying to reference pivot table cells in another worksheet; a direct cell
reference doesn't work because the data is ever-changing. I figured out the
formula if I'm in the same tab, but not from a secondary worksheet.

The same-tab formula is: =GETPIVOTDATA("Budget",$A$4,"Office","ATL")

How do I re-write that to work from another worksheet? I have many others
to do, but I need the basic structure to follow.

Thanks for your help.