GetPivotData - Can't retrieve the subtotals

[Brian]

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 ]

[Aladin Akyurek]

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?

[Brian]

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

[Aladin Akyurek]

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.

[Brian]

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

[Aladin Akyurek]

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

[Brian]

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}

[Brian]

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

[Aladin Akyurek]

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?

[Mark W.]

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 ]