GETPIVOTDATA Excel 2003 Problem

[Randall]

Hello Everyone,

I am having trouble using the GETPIVOTDATA function to obtain data from
a pivot table containing the occurrence of monthly maintenance events
(fields of interest include month, location, whether the event is
severe? [a true / false or Y / N field], # of occurrences [can often be
zero, as sometimes events or severity is infrequent]).

The Pivot table appears okay (enabled reture zero option), but
GETPIVOTDATA yields a #REF error in years where occurrences are
infrequent from December to an earlier month in the year. The table or
GETPIVOTDATA function appears to be outsmarting itself, as these zeros
are real data). When I construct a GETPIVOTDATA function, not including
the check for severity - TRUE / FALSE or Yes / No check, it will return
yield data, but this check yields the error when data is not observed
in the entire pivot table from December of to an earlier month in any
particular year.

I have read about work arounds that include the:

=IF(ISERROR(your_formula),"0",your_formula)

formula to work around this, but this seems so clunky and may hide
other errors.

Any advice? Thanks Randall.

 

[Bvdman32]

You don't have to use the GETPIVOTDATA function to get data from a
pivot table. However, it will automatically generate if you type "="
and then click in a pivot table though. I would type your formula and
manually enter the column and row combination, then you can fill right
or fill down. (i.e. =A4*B4, with A4 and/or B4 in the pivot table). This
might work....

 

[Randall]

Using direct links is not really a satisfactory solution. What if I
want to change the look of my pivot table? Will the links
automatically update? Adding and removing (or hiding, is that the
right word?) certain fields might screw this up too, right?

Why does GETPIVOTDATA assume that large regions of zeros in a Pivot
Table are errors? This is the root cause of my problem, I fear. I am
getting a similar error in a different manner in a different section of
my sheet.

Any other insight?

 

[Randall]

Debra seems fairly sharp at these Pivot Table Problems. You around?
Debra?

Now my GETPIVOTDATA table is selectively choosing to return #REF
errors in certain cases when much of the pivot table contains a lot of
zeros, but not in other cases. If I save, close, reopen and drag /
copy working formulas into the #REF cells data will appear (YAY).
However, after I refresh the PIVOT talbes, the #REF error returns to
the same cells that had errors. It is very odd because it only seems to
occur in certain months and not others. Do I have to get the
arrangement of the Pivot Table just right to get the GETPIVOTDATA to
find the right cells?

 

[Debra Dalgleish]

Are the months the result of grouped dates?
In the GETPIVOTDATA formula, do you reference cells for the item values,
or are you typing them into the formula?

 

[Randall]

I have been using reference cells for triggering the GETPIVOTDATA
function to find the data in the Pivot Table (so, yes, I think).
However, those values I have changed after the fact. If I do the
direct call to a cell. type = and then enter the cell (all the items
are text) the cell still will give a #ref error. I am pretty sure
referencing is not the issue.

"Grouped dates?" Not sure what you mean. Each month and location might
have an entry for a T or F (severe?) event. Sum total the number of
events for the month too.

Am I describing this clearly?

 

[Randall]

The oddest new occurrence is the following:

=GETPIVOTDATA("Report",$A$5,"IncidentDate",4,"ProductAttributable?","Attributable","Customer","XYZ","Severe","Yes","Years",2000)

Yields the correct value for April 2000, Customer XYZ Attributable &
Severe events = 0

=GETPIVOTDATA("Report",$A$5,"IncidentDate",5,"ProductAttributable?","Attributable","Customer","XYZ","Severe","Yes","Years",2000)

Yields the error #REF, which is not the number of May 2000 Customer XYZ
Attributable & Severe events which is zero (and I can see the correct
value in the Pivot Table).

For some reason, If I save and reopen the file then recopy the formula
with the error, the correct answer will return momentarily up until I
hit the refresh button on the pivot table (which causes the error to
reappear). Weird!

 

[Debra Dalgleish]

Maybe the question mark character in the ProductAttributable? field name
is causing the confusion, since Excel uses it as a wildcard character in
some circumstances.

In a copy of the file, can you change the field name, and see if the
problem persists?