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:
<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.
<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col></colgroup>
NOW, I EXPAND TO ADD A COLUMN:
<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.
<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
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