#NAME Error for Calculated Fields after adding columns to source data

Re: #NAME Error for Calculated Fields after adding columns to source data

i have heard of erros like this when dealing with a spreadsheet that was created using an earlier version of excel. not sure if this is the case, but if so, you can try redoing the calculated field and that could help.

Re: #NAME Error for Calculated Fields after adding columns to source data

I can't replicate that. Can you provide a sample and the exact steps you take?

Re: #NAME Error for Calculated Fields after adding columns to source data

Seems to be related to your field names. Changing them to say Field1, Field2 etc seems to resolve the problem for me. Not sure why though - will do some testing.

Re: #NAME Error for Calculated Fields after adding columns to source data

The easiest option I think is to not insert the data between existing columns but add it to the end. That does not seem to have the same effect.

Re: #NAME Error for Calculated Fields after adding columns to source data

Probably above problem comes when you have calculated field items in your pivot data field.
To avoid it you will have to modify the calculated fields used in pivots, just retype whatever fields used and their formula likewise you add new fields in pivot table and modify them.

Hope this helps to resolve the above problem

Re: #NAME Error for Calculated Fields after adding columns to source data

I came upon this thread as I have the same error, however, mine was introduced by a different action. I had read about the technique to make the input range dynamic by using a range name, and defining that range name with an offset from $A$1 and a counta for the number of rows.

In my particular pivot table, all presented fields are calculated, and I had renamed them to get rid of the "Sum of" part in their names. After the change of the data source to the identical, but now named range, ALL my pivot values went to #NAME?. I have not been able to restore them since. Not by returning to a fixed range, not by restoring the field name, not by renaming to Field1. Worse yet, if I rebuild the pivot in the same workbook, all native fields can be dragged into the new pivot, but any of the calculated fields now immediately go to #NAME?.

What is even more bizarre, but may make sense to someone better versed with the internals of Excel, if I double click on any of the #NAME? values, I still get the tab with the correct detail data behind that particular location.

My environment is Win7 Pro 64 bit on a very recent HP ZBook laptop, 16 GB RAM, Office 2010 64bit.

I am carefully rebuilding my work in a new workbook with many saves as this pvot feeds another report series, but this oneintrigues me to where I would love to learn of an active resolution if one will be presented.

My thanks to all who spend a few of their mental CPU cycles thinking about this.

John

Re: #NAME Error for Calculated Fields after adding columns to source data

An additional discovery since I posted my #NAME? in Pivot query.

My calculated fields are all divisions (a possible common ground). When I look to edit one of my "broken" calculated fields, it shows as = #NAME? / #NAME?. When I edit it to put the correct column names back into the division, the line is fixed in the pivot table.

It looks to be that my action (introducing a named range), and other users' actions (inserting columns) may somehow trigger a one time destructive event with regard to the references to calculated field names.

John