GETPIVOTDATA, a few short queries
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: GETPIVOTDATA, a few short queries

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi.

    I am using the GETPIVOTDATA() function to retrieve data from my pivot tables, but am having a few little difficulties which are holding back the whole project (whose deadline is fastly approaching, so quick responses will be appreciated SO much!).

    Here goes

    =IF(ISERROR(GETPIVOTDATA(INDIRECT($A$3&"A1"),$A$7&" "&C$9&" "&C$10&" "
    &$B11&" Sum of NUM"))=TRUE,"",GETPIVOTDATA(INDIRECT
    ($A$3&"A1"),$A$7&" "&C$9&" "&C$10&" "&$B11&" Sum of NUM"))

    is too ugly and writing such formulas like this puts my colleagues off. The reason I need the iserror is that sometimes the data I am trying to retrieve is not there in the pivot table, so I get REF! etc. If the data is not in the pivot table then I want it to return a blank cell like the above. It works fine, but I would like to hear some suggestions about how one would construct a more 'user-friendly' and easy on the eyes version.

    Next query:

    =GETPIVOTDATA(A1,"6 N PP YV Sum of NUM")
    &
    =GETPIVOTDATA(A1,"N PP YV 6 Sum of NUM")

    works but
    if I add another field to the pivot table, the field being a column of numbers, then:

    =GETPIVOTDATA(A1,"6 N PP 2 YV Sum of NUM")

    &
    =GETPIVOTDATA(A1,"2 N PP YV 6 Sum of NUM")

    do NOT work. This function doesn't seem to like it when there are two fields of numbers it seems.

    Can anyone help?

    thanks in advance,

    RET79






    [ This Message was edited by: RET79 on 2002-04-08 07:07 ]

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

    This function doesn't seem to like it when there are two fields of numbers it seems.
    GETPIVOTDATA doesn't tolerate any item ambiguities -- number or text. You'll have to re-itemize one of the numeric fields. For example, if one of the fields is 'Month' use "Feb" instead of 2.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the prompt reply Mark.

    Unfortunately, the numbers aren't months and also they are similar numbers to that of the other column.
    How could I go about setting up a non-ambiguous field? Would I have to set up a calculated field perhaps? I am not entirely sure what ambiguous means in this respect, is it ambiguous to have two columns of numbers in a pivot table or is it amiguous to have two columns of numbers with the same/similar number(s) in each, is that what you mean?

    Thanks again,

    RET79

    [ This Message was edited by: RET79 on 2002-04-08 07:51 ]

  4. #4
    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-04-08 07:48, RET79 wrote:
    Thanks for the prompt reply Mark.

    Unfortunately, the numbers aren't months and also they are similar numbers to that of the other column.
    How could I go about setting up a non-ambiguous field? Would I have to set up a calculated field perhaps? I am not entirely sure what ambiguous means in this respect, is it ambiguous to have two columns of numbers in a pivot table or is it amiguous to have two columns of numbers with the same/similar number(s) in each, is that what you mean?

    Thanks again,

    RET79

    [ This Message was edited by: RET79 on 2002-04-08 07:51 ]

    You item values are ambiguous as GETPIVOTDATA arguments because you can't distinguish which value belongs to which field. Change the items of 1 of the 2 fields in your data list so that they are distinct. For example, instead of...

    {"Field1","Field2"
    ;1,2
    ;2,3
    ;3,1}

    ...you could use...

    {"Field1","Field2"
    ;1,"Two"
    ;2,"Three"
    ;3,"One"}

    ...Or...

    {"Field1","Field2"
    ;1,"2.0"
    ;2,"3.0"
    ;3,"1.0"}

    You'll have to decide what makes sense!


    [ This Message was edited by: Mark W. on 2002-04-08 08:03 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, cheers again Mark, you have been excellent, much appreciated.

    However, any ideas about making the initial forumla less ugly.

    I like to make things look ELEGANT !!

    Well, I try to anyway

    RET79

  6. #6
    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-04-08 08:11, RET79 wrote:
    Ok, cheers again Mark, you have been excellent, much appreciated.

    However, any ideas about making the initial forumla less ugly.

    I like to make things look ELEGANT !!

    Well, I try to anyway

    RET79
    Decompose your long formula into 2 by returning the results of GETPIVOTDATA to a cell (e.g., A1). Then in B1 use the formula...

    =IF(ERROR.TYPE(A1)=4,"",A1)

    If you don't want to use a separate worksheet function that assign your GETPIVOTDATA function to a defined name (e.g., "Pivot_Data"), and use the formula...

    =IF(ERROR.TYPE(Pivot_Data)=4,"",Pivot_Data)

    [ This Message was edited by: Mark W. on 2002-04-08 08:20 ]

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
  •  

 

 
DMCA.com