Formula's Changing to Values on their own

[alexanderhales]

I'm the Network Administrator at my company and am not an excel expert. One of my users is having a really weird problem with excel spreadsheet that we can't seem to figure out. Without doing anything, formulas will change to values on their own.

At first I was absolutely convinced that the user had to be doing something (I didn't know what) to cause the formulas to change to values. But more then a few times now I've stood over her shoulder and watched as the formulas changed themselves. I don't know how to explain it. One minute their formulas and the next minute their values.

The most recent one I witnessed, she clicked on a cell containing a formula so that I could see that they were in fact formulas. Then she clicked on another cell. Then when she clicked back on the first cell to view the formula again, it was no longer a formula but the value of that formula.

Things we've tried:
- We've manually recreated the excel spreadsheet from scratch to see if that would fix it and it hasn't.
- We've changed the file formats to older and newer versions, and that hasn't fixed it.
- We've swapped out the computer, but it still happens regardless of the machine we use.

The version of Excel we're using is 2007. The only thing we haven't tried is a different version.

I'm at a loss... any suggestions?

 

[mole999]

any user add ins, personal xla, macros in the book, weird iterations on recalculate

 

[alexanderhales]

No user add ins, no personal xla, no macros, or weird iterations. Its a very basic spread sheet with multiple tabs.

That might be a clue to what's going on though. The first tab is a summary of all the other tabs. It pulls values from the other tabs and adds them together on the first tab. Its that first tab and the formulas on it that are changing to values.

 

[SpillerBD]

Do the formulas include the file&Sheet reference or just the Sheet reference?
What is the file extension?

 

[alexanderhales]

Just the sheet reference. At one point be found a reference to a file&sheet, but that was a mistake and we removed it.

The file format is .xlsx, we've tried changing formats too

 

[SpillerBD]

I'm glad I can walk away from this. Most anything I think of gets ruled out considering the steps you've taken. (I hope I never see it in our office!)

Starting a new file rules out some options that might control WorkBook or Worksheet behavior.
New/Different computer rules out installation issues for Excel and Excel options. Unless installing from a network, this should rule out the hidden macro possibilities.
I've never heard of this either. Excel is formula intensive program and keeping variables/formulas is essential. (Hello choir)

I doubt you are using a keyboard that is macro capable, but that could have a keystroke recorded that could copy/paste values...?

I am assuming Excel is running as the local desktop app and not a Office365 or RDP session.
I am also assuming no one is trying a practical joke (cause at this point I'm sure it is VERY UN_Funny !)

 

[alexanderhales]

Lol, no... that wouldn't be funny at all (although at this point I am laughing hahaha)

This file used to be located on a file server and at the time was opened and edited by multiple people. So one of the first things we tried was to move the file off of the server and on to a single desktop where only one person could open and edit the file. That didn't resolve it, so we then tried everything else I mentioned above.

It can't be a keyboard issue because we've swapped out the keyboard at least two times since this started. We've also had it happen on different desktops that have different keyboards.

We are not using Office365

We've had this problem occur on local desktops, RDP sessions, and on VDI sessions. Its really bizarre

 

[B___P]

Try to move again the file from the server and stop formula recalculation. Test if it makes any difference.

Bye

 

[SpillerBD]

This file used to be located on a file server and at the time was opened and edited by multiple people. So one of the first things we tried was to move the file off of the server and on to a single desktop where only one person could open and edit the file. That didn't resolve it, so we then tried everything else I mentioned above.

This means that sharing for the file has been turned on. Have you turned sharing off?
(Just grabbing at straws, but hopefully something new will find the issue.)

 

[alexanderhales]

B__P, its not on the server anymore. What we do now is one person has it on their local desktop and all changes are made to that file. Then if anyone needs to see it, she puts it on the server for others to see. But we've never tried formula recalculation, so ill give that a go!


SpillerBD, how do you turn of sharing on an excel file? I didn't even know excel has a sharing option for individual files.