software

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 quot;=Concatenate.....quot; 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 gt;,
tcarp gt; wrote:

gt; In some cases Excel (2003 SP2) is displaying the formula in a cell, not the
gt; results. The formula (say Concatenate or an IF) is correct and there is no
gt; error being indicated. In one case I entered a simple Concatenate and got
gt; the whole quot;=Concatenate.....quot; displayed in the cell. I stumbled on a
gt; workaround by entering the formula in the second cell in a column where the
gt; formula did not display and then copying the formula to the first cell in the
gt; column.
gt;
gt; On another worksheet its not been so simple. I have a relatively complex
gt; IF(ISNA(INDEX(MATCH) formula that displays fine. If I edit the formula to
gt; pull out a portion (in this case the ISNA part), I get the formula displayed.
gt; I've even tried to reenter the formula from scratch but get the formula
gt; displayed.
gt;
gt; I can't figure out a pattern here. The formulas are valid and run from very
gt; simple to somewhat complex so I don't think it has anything to do with the
gt; formulas themselves.
gt;
gt; Any ideas?
gt;
gt; 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
lt;Formatgt;lt;Cellsgt;lt;Number tabgt;
Under Category: make sure Text is NOT selected (use whatever is appropriate).

Does that help?

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

XL2002, WinXP-Proquot;tcarpquot; wrote:

gt; In some cases Excel (2003 SP2) is displaying the formula in a cell, not the
gt; results. The formula (say Concatenate or an IF) is correct and there is no
gt; error being indicated. In one case I entered a simple Concatenate and got
gt; the whole quot;=Concatenate.....quot; displayed in the cell. I stumbled on a
gt; workaround by entering the formula in the second cell in a column where the
gt; formula did not display and then copying the formula to the first cell in the
gt; column.
gt;
gt; On another worksheet its not been so simple. I have a relatively complex
gt; IF(ISNA(INDEX(MATCH) formula that displays fine. If I edit the formula to
gt; pull out a portion (in this case the ISNA part), I get the formula displayed.
gt; I've even tried to reenter the formula from scratch but get the formula
gt; displayed.
gt;
gt; I can't figure out a pattern here. The formulas are valid and run from very
gt; simple to somewhat complex so I don't think it has anything to do with the
gt; formulas themselves.
gt;
gt; Any ideas?
gt;
gt; 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) = quot;=quot; Then
r.NumberFormat = quot;Generalquot;
r.Formula = r.Value
Count = Count 1
End If
End If
Next
MsgBox (Count amp; quot; cells changedquot;)
End Sub

and run it. It should convert all your pseudo-formulae into real ones.
--
Gary's Studentquot;tcarpquot; wrote:

gt; In some cases Excel (2003 SP2) is displaying the formula in a cell, not the
gt; results. The formula (say Concatenate or an IF) is correct and there is no
gt; error being indicated. In one case I entered a simple Concatenate and got
gt; the whole quot;=Concatenate.....quot; displayed in the cell. I stumbled on a
gt; workaround by entering the formula in the second cell in a column where the
gt; formula did not display and then copying the formula to the first cell in the
gt; column.
gt;
gt; On another worksheet its not been so simple. I have a relatively complex
gt; IF(ISNA(INDEX(MATCH) formula that displays fine. If I edit the formula to
gt; pull out a portion (in this case the ISNA part), I get the formula displayed.
gt; I've even tried to reenter the formula from scratch but get the formula
gt; displayed.
gt;
gt; I can't figure out a pattern here. The formulas are valid and run from very
gt; simple to somewhat complex so I don't think it has anything to do with the
gt; formulas themselves.
gt;
gt; Any ideas?
gt;
gt; 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

quot;Gary''s Studentquot; wrote:

gt; Some of your cells may be formatted as Text. Format them as general prior to
gt; entering formulae. If you have already entered cells that you want to
gt; convert, then enter:
gt;
gt; Sub formfix()
gt; Dim r As Range
gt; Count = 0
gt; For Each r In ActiveSheet.UsedRange
gt; If Application.IsText(r.Value) Then
gt; If Left(r.Value, 1) = quot;=quot; Then
gt; r.NumberFormat = quot;Generalquot;
gt; r.Formula = r.Value
gt; Count = Count 1
gt; End If
gt; End If
gt; Next
gt; MsgBox (Count amp; quot; cells changedquot;)
gt; End Sub
gt;
gt; and run it. It should convert all your pseudo-formulae into real ones.
gt; --
gt; Gary's Student
gt;
gt;
gt; quot;tcarpquot; wrote:
gt;
gt; gt; In some cases Excel (2003 SP2) is displaying the formula in a cell, not the
gt; gt; results. The formula (say Concatenate or an IF) is correct and there is no
gt; gt; error being indicated. In one case I entered a simple Concatenate and got
gt; gt; the whole quot;=Concatenate.....quot; displayed in the cell. I stumbled on a
gt; gt; workaround by entering the formula in the second cell in a column where the
gt; gt; formula did not display and then copying the formula to the first cell in the
gt; gt; column.
gt; gt;
gt; gt; On another worksheet its not been so simple. I have a relatively complex
gt; gt; IF(ISNA(INDEX(MATCH) formula that displays fine. If I edit the formula to
gt; gt; pull out a portion (in this case the ISNA part), I get the formula displayed.
gt; gt; I've even tried to reenter the formula from scratch but get the formula
gt; gt; displayed.
gt; gt;
gt; gt; I can't figure out a pattern here. The formulas are valid and run from very
gt; gt; simple to somewhat complex so I don't think it has anything to do with the
gt; gt; formulas themselves.
gt; gt;
gt; gt; Any ideas?
gt; gt;
gt; gt; 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!

quot;tcarpquot; wrote:

gt; That was it (it appears-I need to look in some other places on other
gt; workbooks where I've had the problem.) I'll just respond here but thanks to
gt; the others who gave similar advice.
gt;
gt; It's a little like a virus. Once it's there and the formula becomes text
gt; it's tough to get rid of. I just created a new column, make sure it was
gt; General format, copied the formula as characters and pasted in the new column
gt; and things started working again.
gt;
gt; Althogh I haven't tracked things back to where the problem originated (I
gt; have some complex ISNA(INDEX(MATCH..... formulas) - a word to the wise. If
gt; the spreadsheets you are working on come from a COTS package (in my case
gt; Quickbooks - no criticism) make sure you have your radar on for Text cells.
gt;
gt; Thanks again to all
gt;
gt; I love this forum
gt;
gt; Tom
gt;
gt; quot;Gary''s Studentquot; wrote:
gt;
gt; gt; Some of your cells may be formatted as Text. Format them as general prior to
gt; gt; entering formulae. If you have already entered cells that you want to
gt; gt; convert, then enter:
gt; gt;
gt; gt; Sub formfix()
gt; gt; Dim r As Range
gt; gt; Count = 0
gt; gt; For Each r In ActiveSheet.UsedRange
gt; gt; If Application.IsText(r.Value) Then
gt; gt; If Left(r.Value, 1) = quot;=quot; Then
gt; gt; r.NumberFormat = quot;Generalquot;
gt; gt; r.Formula = r.Value
gt; gt; Count = Count 1
gt; gt; End If
gt; gt; End If
gt; gt; Next
gt; gt; MsgBox (Count amp; quot; cells changedquot;)
gt; gt; End Sub
gt; gt;
gt; gt; and run it. It should convert all your pseudo-formulae into real ones.
gt; gt; --
gt; gt; Gary's Student
gt; gt;
gt; gt;
gt; gt; quot;tcarpquot; wrote:
gt; gt;
gt; gt; gt; In some cases Excel (2003 SP2) is displaying the formula in a cell, not the
gt; gt; gt; results. The formula (say Concatenate or an IF) is correct and there is no
gt; gt; gt; error being indicated. In one case I entered a simple Concatenate and got
gt; gt; gt; the whole quot;=Concatenate.....quot; displayed in the cell. I stumbled on a
gt; gt; gt; workaround by entering the formula in the second cell in a column where the
gt; gt; gt; formula did not display and then copying the formula to the first cell in the
gt; gt; gt; column.
gt; gt; gt;
gt; gt; gt; On another worksheet its not been so simple. I have a relatively complex
gt; gt; gt; IF(ISNA(INDEX(MATCH) formula that displays fine. If I edit the formula to
gt; gt; gt; pull out a portion (in this case the ISNA part), I get the formula displayed.
gt; gt; gt; I've even tried to reenter the formula from scratch but get the formula
gt; gt; gt; displayed.
gt; gt; gt;
gt; gt; gt; I can't figure out a pattern here. The formulas are valid and run from very
gt; gt; gt; simple to somewhat complex so I don't think it has anything to do with the
gt; gt; gt; formulas themselves.
gt; gt; gt;
gt; gt; gt; Any ideas?
gt; gt; gt;
gt; gt; gt; 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 Govierquot;staces1quot; gt; wrote in message
...
gt;I have the same style of problem.
gt; The whole sheet is being treated as text and I cant seem to override
gt; this.
gt; It seems to have begun when I have conducted a series of complex
gt; formlas
gt; across the entire shet. The original data had numbers and then after
gt; applying
gt; the formulas the I cut a large amount of data to a new sheet. This new
gt; data
gt; is now treated as text even though I tell it otherwise.
gt; Dates and formulas are being displayed as text.
gt;
gt; I need help!
gt;
gt; quot;tcarpquot; wrote:
gt;
gt;gt; That was it (it appears-I need to look in some other places on other
gt;gt; workbooks where I've had the problem.) I'll just respond here but
gt;gt; thanks to
gt;gt; the others who gave similar advice.
gt;gt;
gt;gt; It's a little like a virus. Once it's there and the formula becomes
gt;gt; text
gt;gt; it's tough to get rid of. I just created a new column, make sure it
gt;gt; was
gt;gt; General format, copied the formula as characters and pasted in the
gt;gt; new column
gt;gt; and things started working again.
gt;gt;
gt;gt; Althogh I haven't tracked things back to where the problem originated
gt;gt; (I
gt;gt; have some complex ISNA(INDEX(MATCH..... formulas) - a word to the
gt;gt; wise. If
gt;gt; the spreadsheets you are working on come from a COTS package (in my
gt;gt; case
gt;gt; Quickbooks - no criticism) make sure you have your radar on for Text
gt;gt; cells.
gt;gt;
gt;gt; Thanks again to all
gt;gt;
gt;gt; I love this forum
gt;gt;
gt;gt; Tom
gt;gt;
gt;gt; quot;Gary''s Studentquot; wrote:
gt;gt;
gt;gt; gt; Some of your cells may be formatted as Text. Format them as
gt;gt; gt; general prior to
gt;gt; gt; entering formulae. If you have already entered cells that you want
gt;gt; gt; to
gt;gt; gt; convert, then enter:
gt;gt; gt;
gt;gt; gt; Sub formfix()
gt;gt; gt; Dim r As Range
gt;gt; gt; Count = 0
gt;gt; gt; For Each r In ActiveSheet.UsedRange
gt;gt; gt; If Application.IsText(r.Value) Then
gt;gt; gt; If Left(r.Value, 1) = quot;=quot; Then
gt;gt; gt; r.NumberFormat = quot;Generalquot;
gt;gt; gt; r.Formula = r.Value
gt;gt; gt; Count = Count 1
gt;gt; gt; End If
gt;gt; gt; End If
gt;gt; gt; Next
gt;gt; gt; MsgBox (Count amp; quot; cells changedquot;)
gt;gt; gt; End Sub
gt;gt; gt;
gt;gt; gt; and run it. It should convert all your pseudo-formulae into real
gt;gt; gt; ones.
gt;gt; gt; --
gt;gt; gt; Gary's Student
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;tcarpquot; wrote:
gt;gt; gt;
gt;gt; gt; gt; In some cases Excel (2003 SP2) is displaying the formula in a
gt;gt; gt; gt; cell, not the
gt;gt; gt; gt; results. The formula (say Concatenate or an IF) is correct and
gt;gt; gt; gt; there is no
gt;gt; gt; gt; error being indicated. In one case I entered a simple
gt;gt; gt; gt; Concatenate and got
gt;gt; gt; gt; the whole quot;=Concatenate.....quot; displayed in the cell. I stumbled
gt;gt; gt; gt; on a
gt;gt; gt; gt; workaround by entering the formula in the second cell in a column
gt;gt; gt; gt; where the
gt;gt; gt; gt; formula did not display and then copying the formula to the first
gt;gt; gt; gt; cell in the
gt;gt; gt; gt; column.
gt;gt; gt; gt;
gt;gt; gt; gt; On another worksheet its not been so simple. I have a relatively
gt;gt; gt; gt; complex
gt;gt; gt; gt; IF(ISNA(INDEX(MATCH) formula that displays fine. If I edit the
gt;gt; gt; gt; formula to
gt;gt; gt; gt; pull out a portion (in this case the ISNA part), I get the
gt;gt; gt; gt; formula displayed.
gt;gt; gt; gt; I've even tried to reenter the formula from scratch but get the
gt;gt; gt; gt; formula
gt;gt; gt; gt; displayed.
gt;gt; gt; gt;
gt;gt; gt; gt; I can't figure out a pattern here. The formulas are valid and
gt;gt; gt; gt; run from very
gt;gt; gt; gt; simple to somewhat complex so I don't think it has anything to do
gt;gt; gt; gt; with the
gt;gt; gt; gt; formulas themselves.
gt;gt; gt; gt;
gt;gt; gt; gt; Any ideas?
gt;gt; gt; gt;
gt;gt; gt; gt; Tom