Pivottable, Calculated Field,

[JasL]

I'm having problems with calculated fields in Excel 2010 when adding columns to the data source. Adding columns results in calculated fields becoming corrupt!

Datasource
DataRep DataSales DataCOS
Tom £300.00 £150.00
Deek £200.00 £100.00
Harry £100.00 £50.00

Calculated Fields
Solve Order Field Formula
1 Margin£ =DataSales+DataCOS
2 Margin% ='Margin£' /DataSales

Pivot
Row Labels Sum of DataSales Sum of DataCOS Margin £s Margin %
Deek 200.00 100.00 300.00 150.00%
Harry 100.00 50.00 150.00 150.00%
Tom 300.00 150.00 450.00 150.00%
Grand Total 600.00 300.00 900.00 150.00%

Note that the calculated field names have been amended in the pivot. I believe this is relevant.

If I add a new field to the data like this:
DataRep DataSales New1 DataCOS
Tom £300.00 New (150.00)
Deek £200.00 New (100.00)
Harry £100.00 New (50.00)

and refresh, the calculated field all show #NAME? in the pivot table.

The calculated field become:
Calculated Field
Solve Order Field Formula
1 Margin£ =#NAME?+#NAME?
2 Margin% =#NAME? /#NAME?

I'm developing reporting that uses multiple calculated field. The reports are evolving that means new fields will be required which will result in #NAME? errors for all the calculated fields in multiple pivot tables!

I've been using Excel & VBA for 20+ years and consider myself as a bit of a geek. I can normally find solutions to my Excel issues but this one is driving me mad!!!!!

Can anybody please help? My sanity depends on it.

 

[JasL]

Workaround:
The code in this post works like a dream!
http://www.mrexcel.com/forum/excel-...ted-field-based-another-calculated-field.html
Thx Yard

The workaround is great, but I would still be interested in knowing why the problem occurs and how to avoid it, If anyone knows.

 

[Yard]

Hi, glad the code worked for you

I never got to the bottom of the issue, so would also be interested to see if anyone else has.