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

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.

In article ,
tcarp wrote:

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

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


"tcarp" wrote:

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

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.
--
Gary's Student


"tcarp" wrote:

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

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

"Gary''s Student" wrote:

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.
--
Gary's Student


"tcarp" wrote:

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

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!

"tcarp" wrote:

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

"Gary''s Student" wrote:

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.
--
Gary's Student


"tcarp" wrote:

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

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.

--
Regards

Roger Govier


"staces1" wrote in message
...
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!

"tcarp" wrote:

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

"Gary''s Student" wrote:

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.
--
Gary's Student


"tcarp" wrote:

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