Pivot Table Calculations return #NAME? error on data table changes--BUT ONLY for formulas based on an immediately preceding formula

[Stepjack]

OK; I hope someone can help with this, because I've built some pretty in-depth reports based on large data sets, and building 60 formulas. Data is updated monthly and the number of rows changes each time. I use an OFFSET formula to capture everything to avoid having to manually reselect data each month. My formulas are fine here. The problem occurs when I try to add a column, which I need to do on occasion. Even though the OFFSET formula works to capture all columns, it destroys SOME of my calculated formulas. As new columnar data is always added to the end, it does not change the order of the previous column fields on which the formulas are based.

After some digging, and recreting in a small test file, I've discovered that the ONLY formulas that 'break' causing ALL component fields of the calculation (both base data and calcs) to be replaced by '#NAME?' are those where the calculation includes a calculation IMMEDIATELY preceding it in the 'Solve Order'

AS AN EXAMPLE:

Item	Dollars	Dollar Chg	Units	Unit Chg
UPC 1	100	-10	50	5
UPC 2	200	20	70	10

<tbody>
</tbody><colgroup><col span="2"><col><col span="2"></colgroup>

Here is the resulting pivot table with calculations created--base data are the first four columns of data. The following formulas are all basic, so I'll just call out that 'Avg Prc % Chg' is based on the division of 'Avg Prc' and the immediately-preceding 'Avg Prc LY'. Also, I have repeated this exact calculation where it does not immediately follow a component calculation--the result is the same.

	DATA	Calculations
Row Labels	$	$ Chg	Units	Unit Chg	Avg Price	Avg Prc LY	Avg Prc % Chg	Pric Chg	Avg Prc % Chg 2
UPC 1	$100	-$10	50	5	$2.00	$2.44	-18%	-$0.44	-18%
UPC 2	$200	$20	70	10	$2.86	$3.00	-5%	-$0.14	-5%
Grand Total	$300	$10	120	15	$2.50	$2.76	-9%	-$0.26	-9%

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col></colgroup>

NOW, I EXPAND TO ADD A COLUMN:

Item	Dollars	Dollar Chg	Units	Unit Chg	Brand
UPC 1	100	-10	50	5	Brand A
UPC 2	200	20	70	10	Brand A

<tbody>
</tbody><colgroup><col span="2"><col><col span="3"></colgroup>

RESULT: The calculated field 'Avg Prc % Chg' immediately following one of its component calculations 'breaks', while the one not immediately following in the 'solve order' is fine.

	DATA	Calculations
Row Labels	$	$ Chg	Units	Unit Chg	Avg Price	Avg Prc LY	Avg Prc % Chg	Pric Chg	Avg Prc % Chg 2
UPC 1	$100	-$10	50	5	$2.00	$2.44	#NAME?	-$0.44	-18%
UPC 2	$200	$20	70	10	$2.86	$3.00	#NAME?	-$0.14	-5%
Grand Total	$300	$10	120	15	$2.50	$2.76	#NAME?	-$0.26	-9%

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col></colgroup>

As I've said, I have 60 formulas, and many pivot tables replicated into many tabs in various formats, so rebuilding the entire file would be sub-optimal.
Is there a way to simply change the solve order? Any other ideas?

thanks,
Steve

 

[Stepjack]

As I am still unable to find a way to change the 'solve order' for calculated fields (only for calculated lists), my workaround solution was to delete those calc fields related to an immediately-prior calc field and re-add them to the end. Only 11 of my 60 formulas needed to be moved. This worked, but I had to rebuild a couple other formulas that contained a deleted/re-added field. And I had to re-add the fields into the pivot table. Not elegant, but functional. Now when I add a column, the calculated fields are not replaced with #NAME? and still function fine.

I'd still love to hear if someone has a better solution, or if Microsoft has a fix for this bug.

thanks,