Help! Formulas Disappearing After Every Save

[Wardancer]

Hello, I have a problem that I can't find any information on online. I hope someone here can help me.

I work with excel workbooks that have several spreadsheets (tabs). On some of the workbooks (no rhyme or reason I can find), any formula that is pulling data from another spreadsheet within the same workbook reverts to the formula's result every time I save the workbook, instead of saving the formula itself. If the formula is calculating from within the same spreadsheet, the formula remains after a save. Only formulas pulling from other spreadsheets lose their formula. This is a critical problem as any time I change data on another spreadsheet within the workbook, the cells on other tabs don't update with the new information and I have to do the formulas all over again.

I hope I'm explaining this clearly... I have run a repair on my Office suite and that didn't help. This problem has occurred on old workbooks as well as brand new ones.

Thank you,
Sarah

 

[Joe4]

Welcome to the Board!

Are you saying the formulas convert to hard-coded values?
Is there any VBA codes in these workbooks?

 

[Wardancer]

yes, the formulas are converting to hard-coded values after I hit save. But only the formulas that are calculating from other tabs in the workbook. The formulas that are pulling from the same tab are still formulas.

I'm not aware of any VBA codes.... unless they happen automatically, I haven't created any...

 

[Joe4]

I'm not aware of any VBA codes.... unless they happen automatically, I haven't created any...

Yes, that is what may be happening. There is special VBA called called "event procedures" which happen automatically. You can make VBA code run automatically on certain events, like Before or After the saving of a file.

Can you tell me what the file extension is? That often gives us clues as to whether or not the file contains any VBA code.

 

[Wardancer]

.xls

And why would the VBA code do that on some workbooks but not others??? This is driving me mad!

Thank you for taking the time to help me, by the way!

 

[Joe4]

And why would the VBA code do that on some workbooks but not others??? This is driving me mad!

Excel does not create VBA code automatically of its own doing without your knowledge, someone would have had to knowingly, physically programming it to behave in that manner.
If you created the file, and did not do that, then that is probably not happening. If someone else had created the file, it is possible that the put some VBA code in there.

.xls is an old Excel file format, which unformatunately does not really help us. Can you resave the file as an .xlsx? Does it allow you to do that?
That will save it as an Excel file without any VBA. And if it chokes on that, it may give us some clues as to what the issue may be.

 

[Wardancer]

Holy cow that fixed it!!!!! I swear I tried saving it as an .xlsx before and it didn't help. Maybe I didn't. Whatever it is, it's no longer losing the formulas!!!! Thank you so much!!!

 

[Joe4]

Huh! That is odd. I did not expect that to fix it, but maybe it did not like it saving to an old Excel format. I was just hoping to gather some more information.
Anyway, glad that it worked.