Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

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?

Check out our Excel Resources

Are the cells formatted as text?

Posted by Paul on November 06, 2001 5:11 AM

Re: Formulas in workbook are showing and won't show results. . .

Posted by Dan on November 06, 2001 5:12 AM

Is there a single apostrophe before the equal sign? If so, remove it.

Re: Are the cells formatted as text?

Posted by Chris Rock on November 06, 2001 5:57 AM

NO, the cells are not formatted as text.
NO, there is no apostrophe.

My guess is that it's corruption. Can anyone verify this?

Re: Are the cells formatted as text?

Posted by Mark W. on November 06, 2001 6:16 AM

If you reference the cell containing the formula
using the ISTEXT worksheet function (e.g.,
=ISTEXT(A1)) what do you get?

Re: Formulas in workbook are showing and won't show results. . .

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

Re: Are the cells formatted as text?

Posted by Chris Rock on November 06, 2001 7:09 AM

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.

Re: Are the cells formatted as text?

Posted by Mark W. on November 06, 2001 8:29 AM

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.

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.