Thread: Calculated fields in Pivot formulas breaking

Hi I have a series of formulas in an Excel Spreadsheet that break in various fashions when the Table they are linked to is modified in any way.

Exampe, I add a column to a table to include new data, and 2 of my formulas will inbvariably break. They have nothing to do with the new data, it just seems that if the table gets modified formulas break. Since I have dozens of formulas in my spreadsheet, I then have to get a list of all formulas, see what is broken, and rebuild the formula. I do this all the time, and its exasperating.

Anyone know a better way? (Macros are NOT an option).

Example:

Calculated fields:
a=pca
b=pia
c=tca
d=tia
Qualified Visits = Total - a-b-c-d

Rate=Qualified Visits / Clicks
QV Rate = Media/Qualified Visits


If anything is modified in the Table, the Qualified Visits formua display #NAME?-#NAME?-#NAME?-#NAME?-#NAME?
and then all formulas relating to Qualified Visits break.

This is just one example. I cannot figure out what this happens at all.

Any thoughts?

This could better answered by allowing us to take a look at your workbook. Can you upload a copy (scrubbed to remove any sensitive data, of course)?

What type of referencing are you using in your formulas? Post a sample of the actual formulas used.