Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Help with GetPivotData
Thanks Thanks: 0 Likes Likes: 0

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

    Default Help with GetPivotData

    Hi,

    I cannot seem to get all parts of my formula to calculate.

    I have created the formula for 243 rows, but even though the formula is supposed to calculate correctly for all rows, it seems to work for some rows but not for the rest where it is supposed to calculate.

    My formula is as follows:

    Works for this:

    =GETPIVOTDATA("Count of Product Code",Pivot_Analysis!$A$3,"Total No of Orders",B2,"Total no Qty Ordered",C2,"Total No of Days",D2,"Total No of Order Lines",E2,"Total No of times picked from a Pallet Location",F2)

    But not this (even though it is supposed to):

    =GETPIVOTDATA("Count of Product Code",Pivot_Analysis!$A$3,"Total No of Orders",B2,"Total no Qty Ordered",C2,"Total No of Days",D2,"Total No of Order Lines",E2,"Total No of times picked from a Pallet Location",F2)

    Formula Sheet:


    [IMG][/IMG]

    Reference Pivot Table:

    [IMG][/IMG]


    Any ideas on how to fix this?

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,795
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Help with GetPivotData

    Those formulas look exactly the same to me. What's different?

    You will get a #REF error if there isn't a cell in the pivot table for the conditions you specify - e.g. your row 3.

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

    Default Re: Help with GetPivotData

    Sorry, would you mind clarifying what exactly you mean by that?

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,795
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Help with GetPivotData

    Your row 3 is looking for a combination that doesn't appear in your pivot table, hence the error. There is no Low entry for "No of times picked from a Pallet Location" within the High entry for "No of Order Lines" in your pivot table.

  5. #5
    New Member
    Join Date
    Nov 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with GetPivotData

    Thanks for clarifying.

    However, for this one:

    [IMG][/IMG]

    in row 123, despite the value supposedly being 6277, it does not seem to register. Any ideas as to why?

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,795
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Help with GetPivotData

    Perhaps one, or more, of the "Low" values is actually "Low " or " Low" with additional spaces? Type = and select that cell and see what the hardcoded GETPIVOTDATA formula is.

  7. #7
    New Member
    Join Date
    Nov 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with GetPivotData

    That does't seem to be the case, I checked. Is there any other error that could be related to this? I read that the getpivotdata formula can be pretty buggy

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,795
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Help with GetPivotData

    Only if you are using custom subtotals, in which case I think all the formulas would error. What was the GETPIVOTDATA formula that Excel produced for you?

  9. #9
    New Member
    Join Date
    Nov 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with GetPivotData

    I am not, I checked that. What exactly do you mean by your second sentence?

  10. #10
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,795
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Help with GetPivotData

    I mean if you type an = sign and then select the cell in the pivot table from which the value should be returned, Excel should create a GETPIVOTDATA formula for you (unless you turned that option off in settings). What is that formula?

Some videos you may like

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
  •