Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: GetPivotData - Can't retrieve the subtotals

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

    Default

    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 ]

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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?

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

    Default

    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. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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. #5
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

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

    Default

    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. #8
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

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
  •