Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: GetPivotData - Can't retrieve the subtotals

  1. #11
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  2. #12
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  3. #13
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default


  4. #14
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #15
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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