Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Results 1 to 6 of 6

Thread: Equivalent to GETPIVOTDATA in PP?

  1. #1
    New Member
    Join Date
    Nov 2015
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Equivalent to GETPIVOTDATA in PP?


    Equivalent to GETPIVOTDATA in PP?
    Hi all

    Done a bit of Googling and looked at things like CUBEVALUE etc but haven't quite worked out how to get GETPIVOTDATA working when some of the values come from cell references.

    I've had to de-personalise this but basically I have:

    =GETPIVOTDATA("[Measures].[Vol]",PivotTableName,"[TableResults].[ResultScored]","[TableResults].[ResultScored].&[Gold]","[TableDates].[Year]","[TableDates].[Year].&[Y2017]")

    Which returns all the Gold results from Y2017. All I want to change is that the [Gold] and [Y2017] are based on values from cells rather than hard-coded. So that when filters are applied the formula will dynamically update if it needs to look at Silver in Y2016 for example.

    Hope that makes sense - I'm sure there must be an obvious change to the syntax but I can't get it to work!

    Thanks

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,601
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Equivalent to GETPIVOTDATA in PP?

    Assuming values in A1 and A2:

    =GETPIVOTDATA("[Measures].[Vol]",PivotTableName,"[TableResults].[ResultScored]","[TableResults].[ResultScored].&["&A1&"]","[TableDates].[Year]","[TableDates].[Year].&["&A2&"]")

  3. #3
    New Member
    Join Date
    Nov 2015
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Equivalent to GETPIVOTDATA in PP?

    Great stuff, thanks Rory!

  4. #4
    New Member
    Join Date
    Nov 2015
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Equivalent to GETPIVOTDATA in PP?

    Hi guys,

    The above solution works fine for text, I'm just trying to get my head round it for numbers. Ideally I just want it to lookup a number in the cell but it looks like GETPIVOTDATA reads PP pivots differently.

    E.g.
    80 reads as [8.E1]
    81 as [8.1E1]
    133 as [1.33E2]
    500 as [5.E2]

    So I'm struggling to write a formula that translates all numbers to a format GETPIVOTDATA can read.

    Can anyone help?

    Thanks

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,601
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Equivalent to GETPIVOTDATA in PP?

    I can't say I've seen that before but maybe I've just been lucky. See if this does it for you:

    SUBSTITUTE(TEXT(A1,"0.#######E##),"+","")

  6. #6
    New Member
    Join Date
    Nov 2015
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Equivalent to GETPIVOTDATA in PP?

    Thanks Rory - works and much more succinct than the big IF statement I had put in place.

    Thanks again for your help!

User Tag List

Tags for this Thread

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