3

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
0

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

|improve this answer|||||
  • 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
0

I was running into this same problem and found a workaround.

  1. Save a copy of your working document before proceeding.
  2. Select your PivotTable and export calculated fields: PivotTable ToolsAnalyzeFields, Items, & SetsList Formulas. This will create a list of all your formulas in a new sheet. You may want to copy this into a separate document for easy reference later.
  3. Create your new column in the source data and update your PivotTable, it will break.
  4. Go through each of your formulas one by one to see which one is producing the error (PivotTable ToolsAnalyzeFields, Items, & SetsCalculated Field → click the drop down on the Name bar).
  5. You'll find there's at least one where the reference in the formula has changed to #NAME where it's looking for data it can no longer find for some reason. Click Modify to edit this formula(s).
  6. Find that same formula in the list of formulas you just exported. Copy and paste the formula in the formula bar. This fixes the broken reference.
  7. Repeat steps 5 and 6 for each of the broken formulas.
  8. You'll have to repeat steps 1 to 7 each time you add a new column to your source data. If you have multiple columns to add, add then all at once so you only have to do this once.

I'll be honest: I don't know why Excel is breaking like this, but this seems to at least fix it.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy