The Formula is displaying, not the result

[Guest]

In some cases Excel (2003 SP2) is displaying the formula in a cell, not the
results. The formula (say Concatenate or an IF) is correct and there is no
error being indicated. In one case I entered a simple Concatenate and got
the whole "=Concatenate....." displayed in the cell. I stumbled on a
workaround by entering the formula in the second cell in a column where the
formula did not display and then copying the formula to the first cell in the
column.

On another worksheet its not been so simple. I have a relatively complex
IF(ISNA(INDEX(MATCH) formula that displays fine. If I edit the formula to
pull out a portion (in this case the ISNA part), I get the formula displayed.
I've even tried to reenter the formula from scratch but get the formula
displayed.

I can't figure out a pattern here. The formulas are valid and run from very
simple to somewhat complex so I don't think it has anything to do with the
formulas themselves.

Any ideas?

Tom

 

[JE McGimpsey]

The pattern is that the cells you're entering the formulae in are
formatted as Text, so XL doesn't try to interpret them as formulae.

Format the cells as General before entering a formula.

 

[Guest]

Since some of the formulas display and some of the results display, it seems
that the cells that are displaying formulas may be formatted as Text....Try
this

Select one of the problem cells
<Format><Cells><Number tab>
Under Category: make sure Text is NOT selected (use whatever is appropriate).

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

Some of your cells may be formatted as Text. Format them as general prior to
entering formulae. If you have already entered cells that you want to
convert, then enter:

Sub formfix()
Dim r As Range
Count = 0
For Each r In ActiveSheet.UsedRange
If Application.IsText(r.Value) Then
If Left(r.Value, 1) = "=" Then
r.NumberFormat = "General"
r.Formula = r.Value
Count = Count + 1
End If
End If
Next
MsgBox (Count & " cells changed")
End Sub

and run it. It should convert all your pseudo-formulae into real ones.

 

[Guest]

That was it (it appears-I need to look in some other places on other
workbooks where I've had the problem.) I'll just respond here but thanks to
the others who gave similar advice.

It's a little like a virus. Once it's there and the formula becomes text
it's tough to get rid of. I just created a new column, make sure it was
General format, copied the formula as characters and pasted in the new column
and things started working again.

Althogh I haven't tracked things back to where the problem originated (I
have some complex ISNA(INDEX(MATCH..... formulas) - a word to the wise. If
the spreadsheets you are working on come from a COTS package (in my case
Quickbooks - no criticism) make sure you have your radar on for Text cells.

Thanks again to all

I love this forum

Tom

 

[Guest]

I have the same style of problem.
The whole sheet is being treated as text and I cant seem to override this.
It seems to have begun when I have conducted a series of complex formlas
across the entire shet. The original data had numbers and then after applying
the formulas the I cut a large amount of data to a new sheet. This new data
is now treated as text even though I tell it otherwise.
Dates and formulas are being displayed as text.

I need help!

 

[Roger Govier]

Hi

It sounds as though the cells where you were pasting, were pre-formatted
as Text.
Format the whole sheet as General, then try pasting again.