Why does formula automatically change to its result

K

Kevlar

OK, I'm stumped.

When I enter a formula into a cell, for instance; (d27*d28/144)*d23
and then enter a number into cell d23, the formula gets replaced by th
value of the result automatically.

I've searched the help files and found no setting that does this. I
reminds me of an old question I've had lingering since I've used exce
and that is why sometimes when you enter a math calculation, fo
intance; =25/5, the number 5 is entered into the cell instead of th
formula.

Anyone
 
D

David McRitchie

Is this a spreadsheet you originally created.
Possible someone else had an event macro for the
worksheet, check if there is code for the sheet
right-click on the sheettab, view code
 
C

Charlie

Kevlar,

Your formula did not get replaced, it simply did the
calculation you asked it to do. The number you see in the
cell is the results of the calculation. If you check in
the Formula window, at the top of the worksheet, you will
see the formula =25/5.

Charlie O'Neill
 
R

Rollin_Again

Are you talking about what appears in the actual cell or what appears i
the cell preview text box just above your Column letters?

If you want the actual cell to show the written formula withou
calculation you must either format the cell as Text or you can leav
the existing format and press CTRL + *`* (this is the single lef
quotation mark which is usually found below your ESC key on the top o
row of the keyboard)

Just use this key combination to toggle between formulas and values



Rolli
 
K

Kevlar

No, it actually is replacing the formula.

I already checked the sheet code and it was empty
 
K

Kevlar

By closing the file and not saving, then reopening, it is now fixed.
wonder thought what causes that??
 
D

David McRitchie

Evidence destroyed, case closed !!!

Were you experimenting with sheet protection and hidden formulas..
 
K

Kevlar

I do have macros to protect and unprotect the sheet and those cells were
locked. I did not use and hidden formulas. ??
 
K

Kevlar

This is boggling my mind. The problem is back.

The wierd thing is I copy a known good formula to one of these cells
for a split second you can see the formula in the formula window an
then it gets changed automatically to the result of the formula
Formula is gone
 
D

Dave Peterson

You could have code under ThisWorkbook and you could have code in another
workbook that's "helping" you.

Try opening excel in safe mode and seeing if that stops the behavior.

Close excel
window start button|Run
excel /safe

Open your file and see what happens.

(Save mode disables macros (helpful and not too helpful!) and other stuff, too.)

If this stops the problem, you'll want to search for that code.
 
D

David McRitchie

opening in Safe mode will also deactivate addins.
Some people want to remove all formulas from a workbook
before sending it out, were you ever doing anything like that.
 
D

Disappointed

.... and what if you don't have the mentioned symbol on the keyboard? (i.e.: non uk keyboard)
Any other way to get the same results

Thanks
 
K

Kevlar

Dave said:
*
Try opening excel in safe mode and seeing if that stops th
behavior.

*
Click to expand...

I tried your suggestion and it still gets overwritten. Since this onl
happens on a few cells I'm beginning to wonder if this is a bug. I a
running the new Excel 2003
 
K

Kevlar

I set security to high (I had it on Low) and of course my macros woul
not run, but this did not clear up the problem. However when I se
security back to low, it now seems to be working good. I am scepticl
(sp.?) though because the problem is very random.

Could I have a virus
 
K

Kevlar

I have been running in safe mode for about 15 minutes now and th
strange behavior is not happening. Allthough the first time I trie
this a few days ago, it WAS happening. Its very random and I've no
been able to establish a pattern.

I keep a backup copy of the formulas in another column and whenever th
originals get written over, I copy from the backup. Whats strange i
that sometimes the formulas get pasted and sometimes the result i
pasted. I've even pressed Paste (Ctrl V) several times and the first
or 4 attempts I get a result and then the 5th attempt I get a formula.

What does running in safe mode do?

Do you have any ideas
 
N

Norman Harker

Hi Kevlar!

Re: "What does running in safe mode do?"

It stops macros from running
It doesn't load customized toolbars
It doesn't load addins
It doesn't load the Personal.xls file
It doesn't load any files in your startup or default file location

The process of running in safe Mode is ideal for tracking whether the
problem is one of a corrupted Excel program or in one of the above.

Here's an excerpt from:
http://support.microsoft.com/default.aspx?scid=kb;en-us;280504&Product=xlw

"This step may be necessary because when you run both Windows and
Excel in Safe mode, Excel is in the most basic mode that is required
to start the program. This means that no other programs or files are
involved in the Excel startup process. Therefore, the problem may be
due to a corrupted Office file that Excel uses to start. If you remove
and then reinstall Office (or stand-alone Excel), it should resolve
this problem."

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
K

Kevlar

It just happened while in safe mode.

Heres what I know.

The behavior is confined to just a few cells in the same column

I have about 25 macros. Most of them to do with moving around th
sheet, printing, locking and unlocking the sheet, opening forms
clearing and resetting the data.

I have 6 forms that facilitate data entry and bring up lists.

The formulas that are overwritten display hours that are calculate
from various lookup tables

The formulas are overwritten whenever a variable is changed that cause
a different result in that cell.

HELP! I'm dumbfounde
 
Top