Pivot table refresh problem

[mindyg]

I have a source data sheet, and a pivot table report which pulls from the
data sheet. The report also has some calculated field in it. When I
"refresh" my report after changing or adding data in the data source, the
report deletes the columns being pulled from the data sheet, puts #NAME? in
all the calculated field columns and give me a message " do you want to
replace the contents of the destination cells.. yes or no". Clicking either
yes or not does not restore the columns.

I'm using Excel 2003.

 

[Shane Devenshire]

So what's the question?

 

[Debra Dalgleish]

It sounds like the items used as column headings in the source data have
changed. When you refresh the pivot table, it tries to add all the new
items as headings.
For example, in the source data you might have had Jan and Feb in a
column with the heading "Month". Then, in the latest data, Mar, Apr and
May were added.

In the pivot table, you have Months in the Column area. Now, when you
refresh the pivot, there are 5 month names, instead of the previous 2.
If there's anything in the column to the right of the pivot table, there
won't be room for the new months, and you'll see the warning. Insert
some blank columns, and it might work correctly.

For the calculated field, did you change any of the column headings in
the source data? If so, the calculated field won't automatically
recognize the new heading, so you'll have to change the formula.