GETPIVOTDATA in Excel 2007

[Guest]

Hello.

I'm using Excel 2007.

I have this formula in a cell:
=GETPIVOTDATA("Overall Rating",$DU$7,"fweek","200829")

When I change the fweek value to a cell refrence as in the formula below, I
get a #REF error (where DY22 contains the value "200829"):
=GETPIVOTDATA("Overall Rating",$DU$7,"fweek",DY22)

What is puzzling is that I have many other formulas just like this that
work. The only difference is that those formulas reference pivot tables that
were originally built in 2003 (the file was converted from 2003 to 2007) and
my formula that errors out is referencing pivot tables built in 2007.

Any thoughts on what the problem could be?

Any assistance is appreciated. Thanks.

 

[Guest]

Hi Jeff,

I can't duplicate your problem. If I change a GETPIVOTDATA static argument
to a cell reference containing that entry everything works fine. If there
are other formulas of the same nature that are working I would look to make
sure the 200829 entry in cell DY22 does infact match an entry in the pivot
table. For example if you enter 200829 followed by a space in DY22 it will
fail. Or if the pivot table entry 200829 is followed by a space but the
entry in DY22 is not you will get an error. Simply put the two entries must
be exactly the same.

 

[Guest]

Hi again,

The longer formula is:

=IF(ISERR(INDEX($C$4:$C$22,LARGE((MAX($E$4:$E$22)=$E$4:$E$22)*ROW($E$4:$E$22),ROW(A1))-3,1)),"",INDEX($C$4:$C$22,LARGE((MAX($E$4:$E$22)=$E$4:$E$22)*ROW($E$4:$E$22),ROW(A1))-3,1))

Again it must be array entered.