Use getpivot to reference row data

[acollier]

Hello,

I have the following getpivot formula. I can get it to correctly reference the right column headings (A5 and A6), but when I try to get it to cell reference one of the rows in the pivot (year) i get a cell reference error. Is it possible to cell reference row data in a getpivot formula?

=GETPIVOTDATA("" & $A9,'Mth-Total'!$A$291,"Year","2015","Brand","A5","SubBrand","A6")

 

[Andrew Poulsom]

Welcome to MrExcel.

Can you give an example of a formula that doesn't work with a cell reference, stating what that cell contains?

 

[acollier]

Welcome to MrExcel.

Can you give an example of a formula that doesn't work with a cell reference, stating what that cell contains?

Sure the formula would be =GETPIVOTDATA("" & $A9,'Mth-Total'!$A$291,"Year",A4,"Brand","A5","SubBrand","A6").

A9 = Category 1
A4 = 2015
A5 = Brand1
A6 = SubBrad2

The pivot is laid out:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Brand1[/TD]
[TD][/TD]
[TD]Brand2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Sub-brand 1[/TD]
[TD]Sub-brand 2[/TD]
[TD]Subbbrand 3[/TD]
[TD]sub-brand 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Category 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Category 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Category 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Category 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Category 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Category 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Category 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Category 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Category 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


When I change the "Year" item from "2015" to a cell reference with correctly formatted 2015 in it, I get a Ref error

 

[Andrew Poulsom]

2015 appear in double quotes in your original formula so the field is text. Does this work for you?

=GETPIVOTDATA("" & $A9,'Mth-Total'!$A$291,"Year",A4&"","Brand","A5","SubBrand","A6")

 

[acollier]

Yes it does. I literally figured it out just as you posted! Thank you for your help anyways