GETPIVOTDATA formula

[miked1978]

I have a pivot table on 1 tab and a table on another tab that references a cell in the pivot table. It works great but I need to be able to drag it down to the next cell but when I do it stays the same. I've read that I need to change it look at the references.

Original Formula
=iferror(GETPIVOTDATA(" BCWR_PrevWk",Pivots!$A$6,"hull","2618"),0)

BCWR_PrevWk is the field name in the pivot
Pivots is the name of the tab
$A$6 appears to be where the pivot starts
Hull is the row label
2618 is the hull number (this is what I think I need to change)

When I drag the formula down I have to change 2618 to 2619 for it to work properly. I have over 1000 of these so that becomes too time consuming

I changed the formula to these:

=iferror(GETPIVOTDATA(" BCWR_PrevWk",Pivots!$A$6,"hull",A$7),0)​

=iferror(GETPIVOTDATA(" BCWR_PrevWk",Pivots!$A$6,"hull", & A$7 &),0)
=iferror(GETPIVOTDATA(" BCWR_PrevWk",Pivots!$A$6,"hull", "& A$7 &"),0)​

but I get a #REF error.

Any help would be greatly appreciated

 

[FormR]

Hi, if A7 contains the value 2618 and A8 contains 2619 - then you can try this copied down.

=IFERROR(GETPIVOTDATA(" BCWR_PrevWk",Pivots!$A$6,"hull",A7&""),0)

 

[miked1978]

Hi, if A7 contains the value 2618 and A8 contains 2619 - then you can try this copied down.

=IFERROR(GETPIVOTDATA(" BCWR_PrevWk",Pivots!$A$6,"hull",A7&""),0)

It works!!!

I really appreciate it!!!

 

[FormR]

It works!!!

Great - Oh, and welcome to the forum!

 

[GraH]

Sorry for repeating the obvious, previous reaction were not loaded in time.

=iferror(GETPIVOTDATA(" BCWR_PrevWk",Pivots!$A$6,"hull","2618"),0)
=iferror(GETPIVOTDATA(" BCWR_PrevWk",Pivots!$A$6,"hull",A$7),0)

Those red parts need to be the same format. Could it be, in the pivot it is TEXT and in your range it is a VALUE (number) or vice versa.