I'm helping a colleague fix a spreadsheet with a similar issue as stated in this ozgrid post.

The problem: Pivot table with 31 calculated fields. Some calculated fields are calculations of the source data while others are calculations of the results of other calculated fields (this is the problematic part).

Example and the only workaround I have so far:

Total Sales $ TY ='Chain Sales Dlrs TY'+'WTD Sales TY'

Total LY Sales $ ='Chain Sales Dlrs LY'+'WTD Sales LY'

Chg Sales $ ='Total Sales $ TY'-'Total LY Sales $'

Total Sales $ TY and Total LY Sales $ use data source values, Chg Sales $ uses the results of Total Sales $ TY and Total LY Sales $ to calculate change. On changing the data source (e.g. changing values, column headings, insert columns, etc.), Chg Sales $ will give a #NAME? error on refresh. If I change Chg Sales $ to be a formula using data source references (i.e. =('Chain Sales Dlrs TY'+'WTD Sales TY')-('Chain Sales Dlrs LY'+'WTD Sales LY')), it works, but there are 20-30 of these to change.

Is there a way to use results of calculated fields in a calculated field and still have it refresh correctly in Excel 2010?

  • Probably not much help to you, but having mocked this up on a small data set and adding your column names, I don't get the #NAME? error on Chg Sales $. To fix this, think I'd start by looking to rebuild the file/pivot table from scratch in a new workbook (if that isn't too big/scary a job!) – Andi Mohr Dec 19 '14 at 13:44
  • Thanks for the tip! I actually handed off the spreadsheet with my workaround a couple days ago. I'll post an answer if I find a solution. – user360767 Dec 19 '14 at 15:05

Go to Pivot table Options, then check the box which says For error values show.

  • Short answers like this one are frowned upon in these sites. The reason is that there is nothing to learn from them. You should take the time to explain in some detail how your suggestion solves the problem at hand, so that future readers,when facing similar but distinct problems, can benefit from this very same anser. – MariusMatutiae May 29 '15 at 10:54

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.