Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
GetPivotData for hidden fields
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
GetPivotData for hidden fields
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/as...091071033.aspx -Ryan 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
GetPivotData for hidden fields
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. "Ryan Christiansen" wrote: 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/as...091071033.aspx -Ryan 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
GetPivotData for hidden fields
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/xlFunctio...tml#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? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
GetPivotData for hidden fields
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" wrote: 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/xlFunctio...tml#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? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
GetPivotData for hidden fields
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. RonB wrote: 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" wrote: 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/xlFunctio...tml#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? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
GetPivotData for hidden fields
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... "Debra Dalgleish" wrote: 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. RonB wrote: 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" wrote: 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/xlFunctio...tml#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? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
GETPIVOTDATA Bug | Excel Worksheet Functions | |||
Pivot table # of fields | Excel Discussion (Misc queries) | |||
Need pie chart with number of fields instead of info in fields | Charts and Charting in Excel | |||
Number of dropdown fields in Excel is limited. I need more. How? | Excel Discussion (Misc queries) | |||
Sorting Spreadsheet with Merged Fields | Excel Discussion (Misc queries) |