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.
Bookmarks