Pivot Table error after renaming a column

[pete_h]

I have a tab with multiple pivot tables. I inadvertently changed a column name on the main spreadsheet and now when I refresh one of the pivot tables has an error "name?" in the rows.

When I changed the column back and refreshed, the pivot table reported the same error. When I copied and pasted the column name from a back-up copy, I had the same error again.

Is there a way to get the pivot table to calculate correctly with the column name changes, without reinventing a new pivot table and coping it over to the tab with my other existing pivot tables ?

 

[CWatts]

Most likely this is due to a calculated field.

If you have a formula on Sheet1 and it references Sheet 2, if you delete Sheet2 your formula will have a #REF error since it can't find the reference. By deleting that column, you forced an error in the calculated field and it doesn't know how to rebuild it.

 

[pete_h]

CWatts - thanks for the reply...

Yes it is a calculated field, but I did not delete the other sheet. (you referenced sheet 2 in your reply)

the error is not a '#ref' error.

 

[CWatts]

That may not have been the best analogy...

Assuming I have this data set:

Sheet3

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:50px;"><col style="width:33px;"><col style="width:37px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="text-align:left; ">Person</td><td style="text-align:center; ">Cats</td><td style="text-align:center; ">Dogs</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:left; ">John</td><td style="text-align:center; ">5</td><td style="text-align:center; ">6</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:left; ">Sally</td><td style="text-align:center; ">3</td><td style="text-align:center; ">6</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:left; ">John</td><td style="text-align:center; ">5</td><td style="text-align:center; ">6</td></tr></tbody></table>

If I set a pivot table and make a calculated field called "Total", I'll give it the following formula:

=Cats +Dogs

If I delete the Cats column and refresh my pivot, I get the #NAME? error. When I look in my calculated field, the calculated field is then:

='#NAME?' +Dogs

That's what I believe happened when you deleted that column and, the analogy I was trying to make was that the error you're experiencing is like a #REF error, in that it won't automatically be corrected when you readd the column. you'll have to manually edit the calculated field in your pivot table to get rid of that #NAME? error, just as you'd have to manually edit a cell with the #REF error.

 

[pete_h]

But when I try to re-edit the calc. field, it only see's the original columns, not the renamed ones.

That means re-doing the whole thing, thus the reason for the question.