Function displays but not the result - cause?????

[Mark]

Using Excel 2007 I'm having a new problem not faced in Excel 2003.

Sometimes when I enter a function and press enter the function formula shows
but not the result.

for example today it was =ADDRESS(L6079,4,1,1,$L$1)

The result should be Sheet1!$D$6082


But it was showing the formula =ADDRESS(L6079,4,1,1,$L$1)

I pressed F9, Ctr/Shft/` (off on, off on), Hit F2, hit the fx button and
checked the function - gave the result ok when the window was open. Closed
and opened the spreadsheet 2 times.

Finally did get it to show the result now.

But what caused it is a mystery. It's happened a few times now.

Any clues?

Cheers
Mark

 

[Niek Otten]

Hi Mark,

This happens when the cell is formatted as text before you enter the formula.
Formatting as General afterwards is not enough; you have to re-enter the formula too (F2,ENTER).

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Using Excel 2007 I'm having a new problem not faced in Excel 2003.
|
| Sometimes when I enter a function and press enter the function formula shows
| but not the result.
|
| for example today it was =ADDRESS(L6079,4,1,1,$L$1)
|
| The result should be Sheet1!$D$6082
|
|
| But it was showing the formula =ADDRESS(L6079,4,1,1,$L$1)
|
| I pressed F9, Ctr/Shft/` (off on, off on), Hit F2, hit the fx button and
| checked the function - gave the result ok when the window was open. Closed
| and opened the spreadsheet 2 times.
|
| Finally did get it to show the result now.
|
| But what caused it is a mystery. It's happened a few times now.
|
| Any clues?
|
| Cheers
| Mark
|
|

 

[Mark]

Yes, that could well be the case. I did notice the cell was formatted as
text, though I don't know why,
as there was a "formula" in the cell prior to the new fuction. It was simply
"=J6079".

Must be a new thing in Excel 2007.

Many thanks for your help

Cheers
Mark

 

[Gord Dibben]

Not new in 2007.

Been around for a long time.


Gord Dibben MS Excel MVP

 

[Mark]

Thanks Gord.

I've been using spreadsheets for a while now, but I've never had to go to
this extent to enter a function. So something has changed. For example, I'm
now wondering if text is assigned to more cells when opening Excel 2003
files. Certainly there are cells that have not specificaly been assigned as
text that seem to be being treated as text now.

Actaully, I am wondering if there was a option to change it.

Cheers, Mark

 

[Spiky]

Excel often screws with itself when opening files created in a
different version of Excel, esp if the file type is upgraded. I see
random strangeness of formats, and sometimes they resist being
changed. As if the format was locked even though there is no
protection turned on.

I haven't tried v2007, but usually you can get through extreme cases
by creating a new file and copying your data over.