GETPIVOTDATA #REF! error XL2003/2010

  • Thread starter Paul Martin - Melbourne, Australia
  • Start date

P

Paul Martin - Melbourne, Australia

Hi, I have a GETPIVOTDATA formula that worked fine in XL2003 and now that I've opened it up (some time later) in XL2010, I'm getting a REF error. Are there compatibility issues between XL versions. If this helps, the formula is:

=GETPIVOTDATA(TEXT($B$1, "@"), INDIRECT($B$2), $D$4, $D5, $B$3, E$3)

where:
B1 = "Qty" (DataField)
B2 = "Table_Supply" (PivotTable)
D4 = "Date" (RowField)
D5 = 1/5/09 (Date item)
B3 = "CategorySupply" (ColumnField)
E3 = "Gipps" (CategorySupply item)

NOTE:
* I no longer have XL2003 so can't ascertain whether that's the issue
* In the Pivot Table, the Values label is "Sum of Qty", not "Qty", but neither work now (and Qty worked previously)
* I've tried the formula replacing cell references with values as just above, including DATE and DATEVALUE functions for the data element

Nothing seems to change the REF error, so pretty keen for any suggestions. Thanks in advance, Paul
 
Ad

Advertisements

P

Paul Martin - Melbourne, Australia

Hi, I have a GETPIVOTDATA formula that worked fine in XL2003 and now that I've opened it up (some time later) in XL2010, I'm getting a REF error. Are there compatibility issues between XL versions. If this helps, the formula is:



=GETPIVOTDATA(TEXT($B$1, "@"), INDIRECT($B$2), $D$4, $D5, $B$3, E$3)



where:

B1 = "Qty" (DataField)

B2 = "Table_Supply" (PivotTable)

D4 = "Date" (RowField)

D5 = 1/5/09 (Date item)

B3 = "CategorySupply" (ColumnField)

E3 = "Gipps" (CategorySupply item)



NOTE:

* I no longer have XL2003 so can't ascertain whether that's the issue

* In the Pivot Table, the Values label is "Sum of Qty", not "Qty", but neither work now (and Qty worked previously)

* I've tried the formula replacing cell references with values as just above, including DATE and DATEVALUE functions for the data element



Nothing seems to change the REF error, so pretty keen for any suggestions. Thanks in advance, Paul

Another thought, any suggested alternatives to GETPIVOTDATA?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top