Pivot Tables and the getpivotdata formula

[Maxxin]

I use a pivottable to organise data in a list and then use th
getpivotdata formula to pull the data into a customised report.

I have run into a problem now where I try to copy the file and create
new one for the present week. I change the source data however th
getpivotdata formulas all now show ref! error. I've checked the formul
and see nothing wrong. Can anyone help me? please copy your response t
(e-mail address removed)

 

[Debra Dalgleish]

Does the pivot table still include the cell that's referenced in the
GetPivotData formula?
Have the field or item names changed? Maybe they include a trailing
space that wasn't there before.

 

[Jeff K]

I am seeing the same kind of problem. I have a monthly file which I resave
each month as a different name. All of the sudden on the most recent resave,
my getpivotdata formulas no longer work, with absolutely no changes to the
file other than the filename. Any suggestions out there?

 

[Debra Dalgleish]

What version of Excel are you using?
What's the GetPivotData formula?
What was the file name previously, and what is the new file name?

 

[Jeff K]

This is in Excel 2000. The file was previously "Deposit Summary - Oct
05.xls" and is now "Deposit Summary - Nov 05.xls"

one of the formulas is:
=GETPIVOTDATA(tables!$BH$3,Haberfeld!G$6&" eom")

 

[Jeff K]

I found the issue, just not sure why it's popped up or how to fix it. In the
formula below if the Haberfeld!G6 cell is text, the formula works, but does
not if this cell is numeric. Any suggestions?

 

[Debra Dalgleish]

Try using the TEXT function in the formula, e.g.:

=GETPIVOTDATA(tables!$BH$3,"'" &TEXT(Haberfeld!G$6,"0")&" eom'")

 

[Jeff K]

Yes, I thought this would work as well, but no success. No idea why it all
of the sudden has a problem with the numeric values, this always worked until
this week.

 

[Debra Dalgleish]

Are you sure the values are the same? Maybe one value contains hidden
character, such as extra spaces.