Worksheet cell displays formula instead of its value--why?

[GaryInOly]

Hi Folks,
this is my first post on this forum, and also my first at using Excel Jeanie; please let me know if I have somehow transgressed.

Second, my problem seems to be ultra-elementary, and that's why I've spent hours trying to solve it on my own. I've used Excel spreadsheets off and on for 20 years or so, and yet I can't figure this out. I'm using Excel 2003/SP3 under XP.

Problem: Column I is displaying formulas instead of the formula values, and I cannot figure out why! (Ignore the cells with * in them; I think it means "undefined value".)

Let's look at row 62: I want I62 to have the same value as N62, but it displays the formula "=N62" instead of N62's value "1600".
NOTE: All cells are text format.
Now look at F62: It has a formula ("=L62") as you can see from the formula table below the sheet table, and the value of L62 ("0630") is correctly displayed for F62.

I made the format of I62 to be the same as F62 using the format painter, just to be sure there wasn't something weird about I62's formatting; I also used the format painter to apply F's format to I.

So I don't think my problem is with I62's format.

Thinking the problem might lie with N62, I set K62 to have a formula that's the same as I62's--namely, "=N62"--and the value substitution is correctly made for the display of K62. So I concluded that there's nothing wrong with using the value contained in N62.

Another thing puzzles me: When I type in the formula for I62 ("=N62"), after I've typed the "2" but before I hit Enter, the "N62" string turns blue, and a blue box with handles appears around cell N62. That tells me that at that stage, Excel KNOWS that I've typed a cell reference in the formula bar.

But, after I hit Enter, the blue shading goes away; and all operations after that on I62 treat the formula I typed as a text string. For example, if I try to copy I62 to say I64, the string "=N62" appears there, too, instead of "=N64".

I have searched many places for why this is happening yet have not gotten any hits. I posted the question in Yahoo!Answers (where I've been active in the Physics forum) but the 3 folks who replied all assumed I was doing something stupid in my formatting or data-entry! I don't blame them! But, by the time I was driven to post there, I'd already tried their suggestions.

Does anyone here have any idea why this is happening?

Thanks for wading through the excruciating detail of my question. -- Gary

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

*

Year

Month

Day Of Month

Wkday

Est. Low Time

AppleTree Low-1335

WestDeck Low-1335

Est. High Time

AppleTree High-1335

WestDeck High-1335

KOLM Low Time

KOLM Low

KOLM High Time

KOLM High

<tbody>
</tbody>

*	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
62	*	2014	11	12	wed	0630	30.6	=n62	=N62	40.7	1600	0630	28	1600	45
63	*	2014	11	13	thu	0300	30.2	*	*	42.1	*	0300	29	1500	43
64	*	2014	11	14	fri	0630	24.6	*	*	42.3	*	0630	16	1430	46
65	*	2014	11	15	sat	0530	23.0	*	*	43.0	*	0530	15	1500	47
66	*	2014	11	16	sun	0700	23.9	*	=N66	43.7	*	0700	16	1500	48
67	*	2014	11	17	mon	0700	25.5	*	=N67	41.0	*	0700	19	1430	50
68	*	2014	11	18	tue	0730	27.1	*	=N68	42.3	*	0730	19	1500	48
69	*	2014	11	19	wed	0500	32.5	*	=n69	45.7	*	0500	30	1500	50
70	*	2014	11	20	thu	0	*	*	*	*	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:31px;"><col style="width:36px;"><col style="width:46px;"><col style="width:49px;"><col style="width:49px;"><col style="width:61px;"><col style="width:73px;"><col style="width:73px;"><col style="width:61px;"><col style="width:73px;"><col style="width:69px;"><col style="width:64px;"><col style="width:45px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F62 =L62 K62 =N62 F63 =L63 F64 =L64 F65 =L65 F66 =L66 F67 =L67 F68 =L68 F69 =L69 F70 =L70 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[RoryA]

Welcome to the forum.

NOTE: All cells are text format.

That's why. You need to change the format to something else if you want a formula to work rather than being treated as literal text.

 

[GaryInOly]

Are you telling me that you can't use string manipulation functions on Text cells?

Then why is my code for F62 working? It is Text and its formula is defined analogous to that for I62.

 

[RoryA]

No, I'm telling you that if you apply a Text format to a cell (Format Cells , Number tab) the cell will treat anything you enter into it as literal text, including a formula. Change the cell format to General, then f2 and Enter to re-enter and calculate the formula.

 

[GaryInOly]

No, I'm telling you that if you apply a Text format to a cell (Format Cells , Number tab) the cell will treat anything you enter into it as literal text, including a formula. Change the cell format to General, then f2 and Enter to re-enter and calculate the formula.

Thanks, Rory! I followed your advice and got the results I wanted.

I also remember wondering at times how to enter a string that Excel kept interpreting as a formula (even though I didn't want it to be); now I see that Formatting the cell as Text before you enter such data will accomplish that.

Gary

 

[mfexcel]

If you are dealing with a large number of formula formatted as text... and need to convert them back into a formula, it could be time-consuming to do it one by one.

Here's a tips for your consideration:

Select the cells with formula formatted as text
(Suggest to change the format back to General first)
CTRL H to open Find and Replace
Find what: =
Replace what: =

Replace all

 

[GaryInOly]

If you are dealing with a large number of formula formatted as text... and need to convert them back into a formula, it could be time-consuming to do it one by one.

Here's a tips for your consideration:

Select the cells with formula formatted as text
(Suggest to change the format back to General first)
CTRL H to open Find and Replace
Find what: =
Replace what: =

Replace all

Thanks for the tip. Your mini-procedure implies that a Replace command causes XL to treat the cells with "replaced" values as though you have re-entered those new values. (I think it also assumes that the first character of each text field is a "=".)

It does raise a question in my mind: Under the scenario you describe (i.e., "If you are dealing with a large number of formula formatted as text... and need to convert them back into a formula . . . "), are there commands you could use to automate that first step--namely, "Select the cells with formula formatted as text"?

IOW, since it would be time-consuming to select those cells manually, does XL give you a way (without writing a macro or a VB procedure) to "multi-select all text cells whose first char = "=" "?

If so, then your tip would really save some time!

 

[mfexcel]

If you want to apply the Find and Replace to the whole worksheet, you can select any one of the cell (just one cell) and then perform the Find and Replace.

Try and see.

 

[GaryInOly]

If you want to apply the Find and Replace to the whole worksheet, you can select any one of the cell (just one cell) and then perform the Find and Replace.

Try and see.

Sure enough! I think it's counter-intuitive . . . but I also see that it's documented in XL Help.

Thanks!