+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : GETPIVOTDATA Ref Error

Hybrid View

MargaretParry GETPIVOTDATA Ref Error 03-14-2012, 11:57 AM
MargaretParry Re: GETPIVOTDATA Ref Error 03-14-2012, 12:03 PM
MargaretParry Re: GETPIVOTDATA Ref Error 03-14-2012, 12:28 PM
  1. #1
    Registered User
    Join Date
    03-14-2012
    Location
    Westbury, New York
    MS-Off Ver
    Excel 2007
    Posts
    3

    Exclamation GETPIVOTDATA Ref Error

    I am trying to combine the pivot data for two brands (BCCK-SOUP and BCCKM) into one result using the GETPIVOTDATA formula.
    The pivot table has Brand columns including BCCKM, BCCK-SOUP, BCCK-PASTA and Grand Total and rows by Qtr.

    (I hope the format I pasted below is legible to you)

    Used Planned Marketing
    Plan Type (Multiple Items)
    Plan Status (Multiple Items)

    Sum of Planned MKT Column Labels
    Row Labels BCCKM BCCK-SOUP BCCK-PASTA Grand Total
    2012 $0 $6,133,358 $1,525,223 $7,658,581
    Q1 $0 $2,423,123 $254,676 $2,677,799
    Q2 $107,419 $469,317 $576,736
    Q3 $608,533 $382,303 $990,836
    Q4 $2,994,283 $418,927 $3,413,210



    Original formula which works to get data for just BCCK-Soup:

    =GETPIVOTDATA("planned mkt",'Trade MKT Pivot'!$A$71,"brand","bcck-SOUP","Quarter","Q1","Year",2012)

    New formula resulting in REF error.

    =GETPIVOTDATA("planned mkt",'Trade MKT Pivot'!$A$71,"brand","bcck-SOUP","brand","bcckm","Quarter","Q1","Year",2012)


    I need to combine BCCK-Soup with BCCKM and when I try it results in a REF error. I know GETPIVOTDATA can only return data that is visible in the pivot table but as you can see it is visible. It is a zero for now but that is going to change soon and I need to set this up now.
    The odd thing is, if I remove BCCKM from the pivot table then the new formula no longer results in an error but of course would not give me the correct result when there is data.
    Last edited by MargaretParry; 03-14-2012 at 12:29 PM.

  2. #2
    Registered User
    Join Date
    03-14-2012
    Location
    Westbury, New York
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: GETPIVOTDATA Ref Error

    oh, obviously. haha thank you. sometimes the answer is so easy but I can't see it.
    how do I combine the two formulas?
    is this right?
    =((GETPIVOTDATA("planned mkt",'Trade MKT Pivot'!$A$71,"brand","bcck-SOUP","Quarter","Q1","Year",2012),GETPIVOTDATA("planned mkt",'Trade MKT Pivot'!$A$71,"brand","bcckm","Quarter","Q1","Year",2012))
    Last edited by MargaretParry; 03-14-2012 at 12:08 PM.

  3. #3
    Registered User
    Join Date
    03-14-2012
    Location
    Westbury, New York
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: GETPIVOTDATA Ref Error

    Beautiful. Thank you. My boss will be impressed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1