Formulas in workbook are showing and won't show results. . .
Posted by Chris Rock on November 06, 2001 3:02 AM
In a workbook I received from someone else, there's a formula that is showing and it won't display the results. It's a simple link to another cell on the first worksheet.
NO I do not have View/Formulas turned on.
NO the workbook is not protected.
NO there are no macros in the workbook.
Is this a matter of corruption in the workbook, or is there a "feature" in Excel that can make it do this?
Posted by Paul on November 06, 2001 5:11 AM
Are the cells formatted as text?
Posted by Dan on November 06, 2001 5:12 AM
Is there a single apostrophe before the equal sign? If so, remove it.
Posted by Chris Rock on November 06, 2001 5:57 AM
Re: Are the cells formatted as text?
NO, the cells are not formatted as text.
NO, there is no apostrophe.
My guess is that it's corruption. Can anyone verify this?
Posted by Mark W. on November 06, 2001 6:16 AM
Re: Are the cells formatted as text?
If you reference the cell containing the formula
using the ISTEXT worksheet function (e.g.,
=ISTEXT(A1)) what do you get?
Posted by Tom Urtis on November 06, 2001 6:21 AM
If other formulas in the worksheet or workbook do not show as formulas but only display values, then your workbook is likely not corrupted.
One other idea, click in that renegade cell and hit F2, then the Home key to go to the beginning of the formula value. See if the entire entry begins with a space, as if someone hit the spacebar and then entered the formula, which would also have the effect you are seeing.
Any luck?
Tom Urtis
Posted by Chris Rock on November 06, 2001 7:09 AM
Re: Are the cells formatted as text?
I did the ISTEXT formula and it returned TRUE. Then, I went to Edit / Clear / Formats and it didn't work. Then I tried a Number format and it didn't take.
THEN, I tried edit / Clear / ALL and I retyped the formula (just a link to a cell on another sheet). Oddly enough, that worked.
What could it be in the cell causing this?
THanks, Mark W. for the suggestion though.
Oh, and what's even odder about it, is that the ISTEXT formula STILL Returns TRUE after the formula started working (showing the result). The result of the formula is the date.
Posted by Mark W. on November 06, 2001 8:29 AM
Re: Are the cells formatted as text?
Chris, once the data type of a cell's contents
is established changing the cell format has no
effect. Just remember... formatting affects
the appearance of the data in the cell not the
nature (or the internal representation of the
data). Once the format was set to something
other than Text the act of editing the cell
provided the opportunity for Excel to reapply
its defaults for data representation. For
example, if you enter ="1" into a cell and then
perform Copy/Paste Special Values then the
cell will contain text (despite the fact that
it looks like a number). If you click on the
formula bar after selecting the cell and do
nothing more than press [ Enter ] the cell
contents will be changed to 1. Prior to this
you could have applied any number format you
wanted and it wouldn't have changed "1" to 1.
