GETPIVOTDATA = #N/A

[Martin James Thornhill]

In Excel 2000, I have used a spreadsheet for over 9 months without issue.

All of a sudden, the GETPIVOTDATA function returns #N/A for 75% of its use,
working normally for the other 25%.

The pivot table, and its underlying data, changes every day to the extent
that another day's transactions appear at the right-hand side of the pivot
data. The incremental label is a date; the analysis labels remain unchanged.

Why has GETPIVOTDATA sudden gone inconsistently stupid and what is the
solution?

 

[Dave F]

It's impossible to give you a correct answer without seeing the formula
you're referring to. However, be aware that the GETPIVOTDATA syntax is
different under XL 2000 than it is under later versions, so if you have
upgraded XL recently, that could be the issue.

See here for an explanation of how the function works among the various XL
versions: http://www.contextures.com/xlPivot06.html

Dave

 

[Martin James Thornhill]

Thank you for the URL.

The problem nevertheless persists!

One of the formulae that work is:
=-GETPIVOTDATA('x.xls'!PIV,"GBP "&$A11&" "&TEXT(B$2,"dd/mm/yyyy"))

One of the formulae that don't work is:
=-GETPIVOTDATA('x.xls'!PIV,"GBP "&$A10&" "&TEXT(B$2,"dd/mm/yyyy"))

where:
PIV is the named range that includes the pivot table. The pivot table is -
and always has been - within range.

A10 and A11 are text strings that correspond (congruently) with row labels
in the pivot table.

B2 is a date, converted into the same format per the pivot table (done
specifically to make it work).

To emphasise:
* this worked for months and has now decided to give up!
* identical formaulae behave inconsistently!
* aaagh!

Re-typing the formula results in error, as does copy-paste from a working
formula.

The function and the pivot table sit on different worksheets in the same
workbook.

The version of Excel is 2000 and always has been.