2

In my pivot table. I have my data setout like this..

                     M1     M2     M3
Country     Item

Australia   Item 1   5      3      3
            Item 2   3      6      7
            Item 3   4      6      6
Australia Total      12     15     16

Belgium
            Item 1   4      5      7
            Item 2   5      8      3
            Item 3   3      7      3
Belguim Total        12     20     13

What im trying to do is get the total of Item 1 in M1 (which is 9) using the GETPIVOTDATA function.

Ive already been able to get the Country Totals for M1 using "Australia Total" "M1".

Keva161
  • 2,623
  • 9
  • 44
  • 68

2 Answers2

0

I thought this was possible, but looking at Excel 2010 help it seems to say that you can only refer to a total that's visible somewhere in the table. So, I think you need another pivot table that has doesn't include countries. Or you could change the one above so that Countries are to the right of Items. Then point your GetPivotData formula at that.

The specific line from the Excel page linked above is the last one: "returns #REF! because there is no total value of beverage sales for Davolio."

EDIT: If you want to use variables in your GetPivotData formulas this Contextures page has some great tips, especially the one at the end about referring to the Data field.

Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
0

Only numbers shown on the sheet can be returned by returned by the GETPIVOTDATA function. In this case the solution could be to drag the Item field to the column. If now a cell with the number 9 is shown, you are able to retrieve it.

Ruut
  • 1,091
  • 1
  • 16
  • 29