Excel won't evaluate legitimate formulas/functions

[sohunter]

Suddenly, in one of my workbooks, formulas aren't being evaluated.

eg

A B FullName
Jane Doe =TRIM(A2 & " " & B2)


The FullName column simply returns the formula. This same formula works fine in other workbooks. (And it's not just this formula...it's any formula. Anybody know what happened?!

 

[cLFlaVA]

Tools > Options > Calculation

Set to Automatic.
I'm not sure why this happens, it happened to me once as well.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[sohunter]

Thanks. Didn't work.

I actually tried that already. It's already set to Automatic. I changed it, and set it back, in case that made a difference, but it didn't.

Hmmmm

 

[petrosky]

Hi sohunter,

Try hitting "CTRL~"

The control key in combination with the tilde key.

Hope this helps.

Peter

Remember- It's nice to be important,
but it's important to be nice

 

[GeoCan]

Sounds like the cell format somehow got changed to Text rather than number or general.

The easiest fix is to use Format Painter:
Select any cell that works, click the paint brush on the tool bar, Click in the cell with the misbehaving formula; (if the "Entire" Spreadsheet is acting up simply format paint from another sheet or workbook that does work...)

 

[xlbo]

I agree with GeoCan but re-formatting now won't work on existing formulae.
If you re-format a cell with this "error" to general, select it and press F2 then ENTER, does it work ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[GlennUK]

Try Tools/Options/View and unticking the Formula option.

If this isn't ticked, then xlbo is right ( see above ).

Cheers, Glenn.

 

[sohunter]

Thanks everyone. I'm not at work today, but will have a look Monday, and see if any of these fixes work.

 

[sohunter]

GlennUK wrote "Try Tools/Options/View and unticking the Formula option." and that was indeed the fix! Thank you. Not sure how it got selected in the first place, but oh well.

 

[Scott02]

If the column containing the formula was inserted to the right of a column that is formatted as text, the column also becomes formatted as text and your formula is displayed rather than calculating. You just have to highlight the column - format cells - general. Then go to the formula, hit F2 and enter. It will calc then. I've had the same problem when I use a file opened into Excel with a Text Import Wizard and one of the columns has to be formatted as text due to content.

Just another alternative.

 

[petrosky]

Control tilde is the shortcut key to Tools, Options, View, check/uncheck formalas...

FYI

Remember- It's nice to be important,
but it's important to be nice

 

[GlennUK]

Hi petrosky,

not necessarily, for my language ( UK English ) it is Ctrl-` ( control left-single-quote, which is above the TAB key on a UK keyboard ). But agreed, it is a useful shortcut, especially for spotting where someone has overtyped a formula.

Cheers, Glenn.