Thanks:  0
Likes:  0

# Thread: GetPivotData - Can't retrieve the subtotals

1. 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.

Brian

[ This Message was edited by: Brian on 2002-05-02 18:50 ]

2. 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.

Brian

[ This Message was edited by: Brian on 2002-05-02 18:50 ]
What formula did you try, which results in a #REF! error?

3. 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

4. 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.

5. 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

6. 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.

7. 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}

8. 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

9. 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?

10. 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.

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 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•