GetPivotData - Can't retrieve the subtotals

Brian

Board Regular
Joined
Apr 24, 2002
Messages
113
GetPivotData - Can't retrieve the subtotals.

In the Excel97 help example, how would I get "March Total Beverages"?

In my PT, it says Sum instead of Total, but I have tried every combination of the column heading and subtotal heading and the words "sum" "sum of" and "total", but I can't retrieve the subtotals. It's driving me nuts.

ex.
WK10
SYS10 MACH1 5
MACH2 10
SYS10 Sum 15
SYS20 MACH1 50
MACH2 100
SYS20 Sum 150
Grand Total 165

How to I get
"SYS10 Sum WK10" = 15
"SYS20 Sum WK10" = 150

I get either #N/A! or #REF!, depending on what combinations I try.


Thanks in advance,

Brian
This message was edited by Brian on 2002-05-02 18:50
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
On 2002-05-02 18:49, Brian wrote:
GetPivotData - Can't retrieve the subtotals.

In the Excel97 help example, how would I get "March Total Beverages"?

In my PT, it says Sum instead of Total, but I have tried every combination of the column heading and subtotal heading and the words "sum" "sum of" and "total", but I can't retrieve the subtotals. It's driving me nuts.

ex.
WK10
SYS10 MACH1 5
MACH2 10
SYS10 Sum 15
SYS20 MACH1 50
MACH2 100
SYS20 Sum 150
Grand Total 165

How to I get
"SYS10 Sum WK10" = 15
"SYS20 Sum WK10" = 150

I get either #N/A! or #REF!, depending on what combinations I try.


Thanks in advance,

Brian
This message was edited by Brian on 2002-05-02 18:50

What formula did you try, which results in a #REF! error?
 
Upvote 0
I've tried things like the following in every combination I could think of:

GetPivotData(PTname,"SYS10 Sum WK10")
GetPivotData(PTname,"SYS10 WK10")
GetPivotData(PTname,"SYS10 Sum of WK10")
GetPivotData(PTname,"WK10 SYS10")
etc.

GetPivotData(PTname,"March Total Beverages")
GetPivotData(PTname,"March Beverages")
GetPivotData(PTname,"March Sum of Beverages")

I would have expected the subtotals to be accessible.

Regards,

Brian
 
Upvote 0
On 2002-05-03 01:45, Brian wrote:
I've tried things like the following in every combination I could think of:

GetPivotData(PTname,"SYS10 Sum WK10")
GetPivotData(PTname,"SYS10 WK10")
GetPivotData(PTname,"SYS10 Sum of WK10")
GetPivotData(PTname,"WK10 SYS10")
etc.

GetPivotData(PTname,"March Total Beverages")
GetPivotData(PTname,"March Beverages")
GetPivotData(PTname,"March Sum of Beverages")

I would have expected the subtotals to be accessible.

Regards,

Brian

Select an unused cell in the worksheet where your pivot table is, type =, select all of the cells of your pivot table, hit F9, copy what you see, and paste it here in the follow up. Hope the table is not too big.
 
Upvote 0
Here is the pivot table:

Sum of hits WK
System task WK_11 WK_12 Grand Total
SYS11 an 2 4 6
SYS11 Sum 2 4 6
SYS21 gh 2 2
we 2 1 3
SYS21 Sum 2 3 5
an Sum 2 4 6
gh Sum 2 2
we Sum 2 1 3
Grand Total 4 7 11

Here is the variety of formulas:
=GETPIVOTDATA($A$3,A15)
WK_11 4
SYS11 #REF!
SYS11 Grand Total #REF!
SYS11 Sum #N/A
Grand Total 11
Sum of hits 11
WK_11 SYS11 #REF!
WK_11 Sum SYS11 #N/A
WK_11 Sum of SYS11 #N/A
WK_11 SYS11 Sum #N/A
SYS11 WK_11 #REF!
SYS11 Sum WK_11 #N/A
SYS11 Sum of WK_11 #N/A
SYS11 WK_11 Sum #N/A
SYS11 an WK_11 2
SYS21 we WK_12 1
an Sum #N/A
an Sum Grand Total #N/A
an Sum WK_11 #N/A

Thanks for you help,

Brian
 
Upvote 0
Here an example of what I wanted you to do:

{0,"Month",0,0,0;"Data",1,2,3,"Grand Total";"Sum of minutes",120,130,90,340;"Sum of USD",705,505,295,1505}

Created with the procedure I described to you.

The range of the above is: A3:E8, which I named PT2. And, also as en example,

=GETPIVOTDATA(PT2,"Sum of minutes 3")

gives me: 90.

Aladin
 
Upvote 0
Sorry, here it is:

{"Sum of hits",0,"WK",0,0;"System","task","WK_11","WK_12","Grand Total";"SYS11","an",2,4,6;"SYS11 Sum",0,2,4,6;"SYS21","gh",0,2,2;0,"we",2,1,3;"SYS21 Sum",0,2,3,5;0,"an Sum",2,4,6;0,"gh Sum",0,2,2;0,"we Sum",2,1,3;"Grand Total",0,4,7,11}
 
Upvote 0
Hi,

How do I get you {} back into range A3:E8?

I tried various thing with enter, F9, and Ctrl-Shift-Enter, but no luck.

My range was A3:E13

Regards
 
Upvote 0
On 2002-05-03 11:48, Brian wrote:
Sorry, here it is:

{"Sum of hits",0,"WK",0,0;"System","task","WK_11","WK_12","Grand Total";"SYS11","an",2,4,6;"SYS11 Sum",0,2,4,6;"SYS21","gh",0,2,2;0,"we",2,1,3;"SYS21 Sum",0,2,3,5;0,"an Sum",2,4,6;0,"gh Sum",0,2,2;0,"we Sum",2,1,3;"Grand Total",0,4,7,11}

Does this work:

=GETPIVOTDATA(PTname,"sys11 wk_11")

where PTname is the name of the report area?
 
Upvote 0
On 2002-05-02 18:49, Brian wrote:
GetPivotData - Can't retrieve the subtotals.

In the Excel97 help example, how would I get "March Total Beverages"?

In my PT, it says Sum instead of Total, but I have tried every combination of the column heading and subtotal heading and the words "sum" "sum of" and "total", but I can't retrieve the subtotals. It's driving me nuts.

ex.
WK10
SYS10 MACH1 5
MACH2 10
SYS10 Sum 15
SYS20 MACH1 50
MACH2 100
SYS20 Sum 150
Grand Total 165

How to I get
"SYS10 Sum WK10" = 15
"SYS20 Sum WK10" = 150

I get either #N/A! or #REF!, depending on what combinations I try.


Thanks in advance,

Brian
This message was edited by Brian on 2002-05-02 18:50

=GETPIVOTDATA(pivot_table,"SYS10 WK10") produces 15
=GETPIVOTDATA(pivot_table,"SYS20 WK10") produces 150

...where pivot_table is a reference to a cell in the PivotTable
This message was edited by Mark W. on 2002-05-03 14:16
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top