formulas display but don't calculate.

[Mr. Z]

I have recently come across a vexing problem regarding formula input in MS
Excel 2003. I have not been able to replicate the problem in previous
versions.

When typing a formula in a cell, the formula itself appears but it does not
calculate. I have looked in numerous places for a simple remedy or
suggestions and the common answer is that the format of the cell in question
needs to be changed from "Text" to "General"....almost any format EXCEPT text.

Well, I have tried to change the format....doesn't work
I have copied the data in the spreadsheet to a NEW spreadsheet where the
format of the blank cells I am entering formulas into is set to
general...doesn't work
I have changed the autocalculation feature to automatic...no help there either

The ONLY thing that works - and not all the time - is pressing F9 and left
mouse clicking on the cell TWICE. I only came up with this by chance and
frustration. I'm working with a lot of data in this spreadsheet and I can't
click on each cell holding F9 simultaneously.

What am I missing? The problem appears to have something to do with
formatting as I can paste an existing cell reference from a neighboring cell
and that formula will calculate BUT if I edit the cell I dragged the formula
to, the formula appears and not the result of the formula.

Can I do anything to correct this annoyance? Has the file that I am working
on been authored in a version of Excel that is not compatible with the
release I am using?

The formula is just a simple cell reference where I'm copying the contents
of a target cell to a destination.

If anyone can give me direction, I would appreciate it as NONE of the posted
remedies are working for me.

 

[BoniM]

Check Tools, Options, View tab and remove the check in Formula under Window
Options.

 

[Gord Dibben]

Simply changing the formatting doesn't do the trick.

Excel must be coerced into changing from Text to General.

Format all to General then select a cell and F2 and ENTER

To do many cells at once hit CTRL + f and replace

what: =

with: =

Replace all.

Usually does the job.


Gord Dibben MS Excel MVP

 

[Mr. Z]

Mr. Dribben,

Thank you very much...that did work. I suspect that the spreadsheet I was
supplied with had formatting that was particularly tough to change. Since I
deal with this supplying entity quite a bit, I am going to print this
solution and place it in a reference binder since others in my organization
have had similar difficulties with files sent to us. There are indications
that some other program is outputting these files and the associated
problems. You should see what they do with ZIP CODES. I have to jump through
hoops to get them in a workable format.

boniM,

Yes, that was one of the suggested solutions I read of. Unfortunately, that
remedy had no effect on this particular spreadsheet. I thought I was going
crazy because everything I tried SEEMED like the logical solution.

Thank you both very much for your prompt response. You both have made this
particular task less daunting and time consuming.

I owe you both adult beverages.

 

[Gord Dibben]

Thanks for the feedback.


Gord

Mr. Dribben,

Thank you very much...that did work. I suspect that the spreadsheet I was
supplied with had formatting that was particularly tough to change. Since I
deal with this supplying entity quite a bit, I am going to print this
solution and place it in a reference binder since others in my organization
have had similar difficulties with files sent to us. There are indications
that some other program is outputting these files and the associated
problems. You should see what they do with ZIP CODES. I have to jump through
hoops to get them in a workable format.

boniM,

Yes, that was one of the suggested solutions I read of. Unfortunately, that
remedy had no effect on this particular spreadsheet. I thought I was going
crazy because everything I tried SEEMED like the logical solution.

Thank you both very much for your prompt response. You both have made this
particular task less daunting and time consuming.

I owe you both adult beverages.