I have a pivot table with Week No as a column field. The info it retrieves is
based on data produced by the WEEKNUM function in another sheet. I have a
GETPIVOTDATA function which retrieves info from the pivot table for a
particular week number which works fine. The column field is currently
filtered to select only weeks 9,10,11,12 and 13.

If I change the filter to include weeks 5,6,7 and 8 and change the
GETPIVOTDATA function to look for information in the week 8 column, it
returns #N/A. I have tried everything I can think of to try to get this to
work to no avail. The only fix is to create a new pivot table from scratch
which includes weeks 5,6,7 and 8.

It almost seems that whatever method the GETPIVOTDATA uses to reference the
data it retrieves the original configuration of fields only. Once you change
the pivot table fields the function loses track of what is going on.

As further evidence, to the new pivot table I created to be able to retrieve
data for weeks 5,6,7 and 8, I removed the Week No field and replaced it with
a Month No field and change the GETPIVOTDATA function to retrieve the data in
the pivot table by month. Once again this returned #N/A. I had to create
another new pivot table with the Month No field in there from the beginning
for the GETPIVOTDATA function to work.

Is this a bug or am I missing something ?

BTW I have XL2000 SP3.

Regards,

IK

Well, well, well. As soon as I lay the proverbials on the line making
statements such as below I go away and mess around some more and get it to
work.:-<

I changed the formula which produces the week number to 2 digits
(TEXT(WEEKNUM(A1,1),"00")) and the GETPIVOTDATA function works like a charm.

hmmmm - "patience grasshopper"..................

"SthOzNewbie" wrote:

I have a pivot table with Week No as a column field. The info it retrieves is
based on data produced by the WEEKNUM function in another sheet. I have a
GETPIVOTDATA function which retrieves info from the pivot table for a
particular week number which works fine. The column field is currently
filtered to select only weeks 9,10,11,12 and 13.

If I change the filter to include weeks 5,6,7 and 8 and change the
GETPIVOTDATA function to look for information in the week 8 column, it
returns #N/A. I have tried everything I can think of to try to get this to
work to no avail. The only fix is to create a new pivot table from scratch
which includes weeks 5,6,7 and 8.

It almost seems that whatever method the GETPIVOTDATA uses to reference the
data it retrieves the original configuration of fields only. Once you change
the pivot table fields the function loses track of what is going on.

As further evidence, to the new pivot table I created to be able to retrieve
data for weeks 5,6,7 and 8, I removed the Week No field and replaced it with
a Month No field and change the GETPIVOTDATA function to retrieve the data in
the pivot table by month. Once again this returned #N/A. I had to create
another new pivot table with the Month No field in there from the beginning
for the GETPIVOTDATA function to work.

Is this a bug or am I missing something ?

BTW I have XL2000 SP3.

Regards,

IK