GetPivotData for hidden fields

[Guest]

Is there a way to retrieve data from a PivotTable if the data isn't currently
visible? I can use GetPivotData, but when the table fileds are re-arranged,
the GetPivotData formula fails because the fields aren't currently visible.

Any way around this?

 

[Ryan Christiansen]

I don't think you can use GETPIVOTDATA to extract data from fields that
aren't currently visible. The Excel 2003 Help says: "If the arguments
do not describe a visible field, or if they include a page field that
is not displayed, GETPIVOTDATA returns #REF!."

http://office.microsoft.com/en-us/assistance/HP052091071033.aspx

-Ryan

 

[Guest]

Yeah, I saw this too.

Is there another function other than GetPivotData? Or another way to tackle
this? I want to be able to calculate a result using data from two different
data sources (therefore two different pivot tables) and still get the
formulas to work even if the pivot tables are re-arranged to display other
fields.

 

[Debra Dalgleish]

If the source data is in Excel, you may be able to use formulas to pull
the data from that. For example, the SumProduct function can total cells
that match multiple criteria:

http://www.contextures.com/xlFunctions01.html#SumProduct

 

[Guest]

No, unfortunately the data are coming from external SQLServer databases.
Other data mining tools are probably more appropriate, but EXCEL is
amazingly powerful (!), and everyone at work has a copy on their PC.

Is it possible in VBA to change the PivotTable layout to get the data I
want, and then restore the PivotTable to it's initial state?

[I wish GetPivotData could retrieve data which is not on the active page of
the table. This command has all the parameters required to define the exact
piece of data desired. I don't understand the reason for this
limitation.....]

Ron

 

[Debra Dalgleish]

You could do that -- programmatically make the items visible for which
you need the GetPivotData values, then copy the results and paste as values.

Or, on a hidden sheet, keep another pivot table, based on the first one,
and leave all the items visible there. Use the GetPivotData formulas to
extract data from that table.

No, unfortunately the data are coming from external SQLServer databases.
Other data mining tools are probably more appropriate, but EXCEL is
amazingly powerful (!), and everyone at work has a copy on their PC.

Is it possible in VBA to change the PivotTable layout to get the data I
want, and then restore the PivotTable to it's initial state?

[I wish GetPivotData could retrieve data which is not on the active page of
the table. This command has all the parameters required to define the exact
piece of data desired. I don't understand the reason for this
limitation.....]

Ron

:

If the source data is in Excel, you may be able to use formulas to pull
the data from that. For example, the SumProduct function can total cells
that match multiple criteria:

http://www.contextures.com/xlFunctions01.html#SumProduct

 

[Guest]

Thanks Debra!

My pivot tables have multiple pages, and I can't make all the pages visible
at once. But programmatically changing the pages is alot easier than
rearranging the enire PivotTable layout. So a hidden worksheet with another
copy of the PivotTable (with all rows and columns visible on each page)
sounds like a workable solution.

But I'm still stumped as to why Microsoft made the GetPivotData VBA command
unable to retrieve data which isn't visible...

You could do that -- programmatically make the items visible for which
you need the GetPivotData values, then copy the results and paste as values.

Or, on a hidden sheet, keep another pivot table, based on the first one,
and leave all the items visible there. Use the GetPivotData formulas to
extract data from that table.

No, unfortunately the data are coming from external SQLServer databases.
Other data mining tools are probably more appropriate, but EXCEL is
amazingly powerful (!), and everyone at work has a copy on their PC.

Is it possible in VBA to change the PivotTable layout to get the data I
want, and then restore the PivotTable to it's initial state?

[I wish GetPivotData could retrieve data which is not on the active page of
the table. This command has all the parameters required to define the exact
piece of data desired. I don't understand the reason for this
limitation.....]

Ron

:

If the source data is in Excel, you may be able to use formulas to pull
the data from that. For example, the SumProduct function can total cells
that match multiple criteria:

http://www.contextures.com/xlFunctions01.html#SumProduct

RonB wrote:

Is there a way to retrieve data from a PivotTable if the data isn't currently
visible? I can use GetPivotData, but when the table fileds are re-arranged,
the GetPivotData formula fails because the fields aren't currently visible.

Any way around this?