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

 

[Brian]

Thanks, but I've tried that.
$A$3 is a cell in the pivot table.
You can see that some values are obtainable, but the subtotals are not. (Since my earlier post, I changed the 0's to 1's to ensure no mistakes in 0 vs. o)

4 =GETPIVOTDATA($A$3,"WK_11")
#REF! =GETPIVOTDATA($A$3,"SYS11")
#REF! =GETPIVOTDATA($A$3,"SYS11 Grand Total")
#N/A =GETPIVOTDATA($A$3,"SYS11 Sum")
11 =GETPIVOTDATA($A$3,"Grand Total")
11 =GETPIVOTDATA($A$3,"Sum of hits")
#REF! =GETPIVOTDATA($A$3,"WK_11 SYS11")
#N/A =GETPIVOTDATA($A$3,"WK_11 Sum SYS11")
#N/A =GETPIVOTDATA($A$3,"WK_11 Sum of SYS11")
#N/A =GETPIVOTDATA($A$3,"WK_11 SYS11 Sum")
#REF! =GETPIVOTDATA($A$3,"SYS11 WK_11")
#N/A =GETPIVOTDATA($A$3,"SYS11 Sum WK_11")
#N/A =GETPIVOTDATA($A$3,"SYS11 Sum of WK_11")
#N/A =GETPIVOTDATA($A$3,"SYS11 WK_11 Sum")
2 =GETPIVOTDATA($A$3,"SYS11 an WK_11")
1 =GETPIVOTDATA($A$3,"SYS21 we WK_12")
#N/A =GETPIVOTDATA($A$3,"an Sum")
#N/A =GETPIVOTDATA($A$3,"an Sum Grand Total")
#N/A =GETPIVOTDATA($A$3,"an Sum WK_11")

Too bad tabs don't show up in the msg board.

I could e-mail you the spreadsheet. You can send your e-mail address to "BW-NoSPAM1@earthlink.net" (so that you don't have to post your address).

 

[Aladin Akyurek]

Brian,

Your pivot table is in a wrong state. It even has a wrong count! I removed all named ranges from the Names in Workbook: they were all prefixed with #REF!, maybe because the WB you sent me a cut-out from a larger one.

I constructed two new pivot tables: one mirroring the original and another similar to it.

Guess what: GETPIVOTDATA works on both of them as advertised.

I have also defined for your data a dynamic name range, Pbase, which you can use as source for all pivot tables that you'd want to contruct from that data.

The WB is underway to you.

Aladin

 

[Brian]

[No message]

 

[Brian]

Aladin,

Thanks, you found me the solution, but the wrong reasoning. Sometimes Excel is bizarre:

When using "Wizard > double-click Field > Subtotals Automatic", then it works as expected (and as you showed).

But, when using "Wizard > double-click Field > Subtotals Custom > Sum", then the sum is not available! (I used used "sum".)

I could not access ANY of the other custom functions (count, average, etc) that I tried (xl97).

(sorry, I cut out the excess sheets before sending the workbook, so that's where all of the leftover names came from, but none of those were used in the table or pivot table. Yes, I the PT missed a line in the table, but was just junk test data to get the spreadsheet working.)


Thanks again,

Brian

 

[Aladin Akyurek]

Thanks, you found me the solution, but the wrong reasoning.

Since I didn't offer any explanation for the "wrong state" of the original pivot table/report, so it's rather "no explanation" than "wrong reasoning". Maybe you took my observation about named ranges as some kind of explanation. I didn't intend that as such.

Sometimes Excel is bizarre:

When using "Wizard > double-click Field > Subtotals Automatic", then it works as expected (and as you showed).

I didn't use that line at all explicitly, just default settings. That boils to the same thing I suppose.

But, when using "Wizard > double-click Field > Subtotals Custom > Sum", then the sum is not available! (I used used "sum".)

I just gave a double click on the 'system' field in the original pivot report/table. It was Custom/Sum, switched to Automatic:

SYS11 Sum becomes SYS11 Total and =GETPIVOTDATA(A3,"SYS11 WK_11") works immediately.

Hope Mark (Mark W., that is) would want to comment on why this would thwart retrieval.

Aladin