Why are my formulas changing automatically?

[Opv]

I went through a workbook last night and changed a number of my formulas to accommodate a new TABLE (in Excel 2007). I got all of the formulas changed, for example, =SUM(REGISTRY[AMT]), then saved my workbook and went to bed. When I got up this morning and opened my workbook, the formula for the example given was changed to =SUM(CHECKING!$H$6:$H$3015). All of my other formulas were similarly changed.

Is there a setting somewhere that is causing this phenomenon? Is there a way to prevent it from occurring? Is there a trick to forcing all of the formulas to change back to the way I entered them without having to re-enter all of them?

Thanks

[Jan Karel Pieterse]

Can you upload a stripped-down version of the file with dummy data?

[Opv]

Thanks for the reply, Jan. I can, but it may not be necessary. I just performed several tests, and it seems that the issue lies in the file formatting. I created a new workbook and saved it in both XLSX and XLSM formats and after closing both files and reopening them, the formulas retained their identification of the Table as entered. However, after saving the file as an older version of Excel (1997-2003), i.e., XLS format, closing and re-opening the file, the formulas were changed in the same manner as my primary workbook (which is also saved in the old format). I then revised a formula in my original workbook, saved the file as a XLSM file, reopened it, and it retained the formula as I had entered it.

I don't have any particular reason for retaining the old format other than the fact the file was created a number of years ago and I never updated it after upgrading to Office 2007. I guess my issue had to do with the lost functionality alert when forcing Excel to retain the old format after upgrading.

If you think it is still important to upload a test file I can do so, but I think just changing the file format has resolved my issue.

Thanks again,

Opv

[Jan Karel Pieterse]

No this totally explains the behaviour and how to prevent it from happening. :-)