evaluate vs. display formula (exel 2k3)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ken Piper - 02 Apr 2004 00:06 GMT

Help, this is really bugging me:

Periodically my (patched up) Excel 2003 suddenly refuses to evaluate my
(VLOOKUP) formulas, and will only display the text of the formula in the
cell. Additionally, the cell reference in the VLOOKUP formula will not
update when I do an autofill to the next cell in the range I am looking up.
I can force an evaluation on an individual cell via F2 then F9, but
unfortunately I lose the formula when I do that and, consequently it is
useless for the lookups on the number of records I have to lookup.

Additionally, using Ctrl+~ will not toggle evaluation of these formulas.
They stay as text.

I have to reboot and restart excel both a few times to get it to go away and
evaluate formulas again.

Anyone with a clue - am I hitting some strange key combination, or is some
checkbox buried somewhere checked?

Thanks!

Signature

Ken Piper
MCP - SQL Server

Reply to this Message

Dave Peterson - 02 Apr 2004 00:37 GMT

The usual suspects are:
1. Viewing Formulas (tools|options|view tab, too)
2. The cell got formatted as text.
3. A leading space(s) before the initial equal sign

It sounds like you ruled out #1.

Try formatting the cell as General and then F2|F9
(or if you have lots of cells, Edit|Replace = with = (replace all)).

> Help, this is really bugging me:
>
[quoted text clipped - 20 lines]
> Ken Piper
> MCP - SQL Server

Signature

Dave Peterson
ec35720@msn.com

Reply to this Message

Ken Piper - 02 Apr 2004 01:11 GMT

Gaah - formatted as text. thanks a bunch!

Signature

Ken Piper
MCP - SQL Server

> The usual suspects are:
> 1. Viewing Formulas (tools|options|view tab, too)
[quoted text clipped - 30 lines]
> > Ken Piper
> > MCP - SQL Server

Reply to this Message

RagDyer - 03 Apr 2004 02:58 GMT

FWIW,

<<"Additionally, using Ctrl+~ will not toggle evaluation of these formulas.
They stay as text.">>
<Ctrl> <~> is a keyboard shortcut to format a cell to "General".
All you had to do after this format change to General, was <F2> <Enter>, and
you would have been home free.

Signature

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Gaah - formatted as text. thanks a bunch!

Signature

Ken Piper
MCP - SQL Server

> The usual suspects are:
> 1. Viewing Formulas (tools|options|view tab, too)
[quoted text clipped - 14 lines]
> > cell. Additionally, the cell reference in the VLOOKUP formula will not
> > update when I do an autofill to the next cell in the range I am looking

up.

> > I can force an evaluation on an individual cell via F2 then F9, but
> > unfortunately I lose the formula when I do that and, consequently it is
[quoted text clipped - 4 lines]
> >
> > I have to reboot and restart excel both a few times to get it to go away

and

> > evaluate formulas again.
> >
> > Anyone with a clue - am I hitting some strange key combination, or is

some

> > checkbox buried somewhere checked?
> >
[quoted text clipped - 3 lines]
> > Ken Piper
> > MCP - SQL Server

Reply to this Message

Dave Peterson - 03 Apr 2004 03:35 GMT

but ctrl-` (ctrl-backquote, to the left of 1/! on my USA keyboard) will toggle
between formulas and normal.

And thanks for the correction.

(F2|F9 should have been F2|enter)

> FWIW,
>
[quoted text clipped - 62 lines]
> > Dave Peterson
> > ec35720@msn.com

Signature

Dave Peterson
ec35720@msn.com

Reply to this Message

RagDyer - 03 Apr 2004 03:37 GMT

And thank YOU for the correction.

<Ctrl> <Shift> <~> is the format shortcut.

However, LITERALLY, it was correct!

Hold <Ctrl> and hit <~>.
How you get to <~> is your problem.<bg>

Signature

but ctrl-` (ctrl-backquote, to the left of 1/! on my USA keyboard) will
toggle
between formulas and normal.

And thanks for the correction.

(F2|F9 should have been F2|enter)

RagDyer wrote:

> FWIW,
>
[quoted text clipped - 60 lines]
> > Dave Peterson
> > ec35720@msn.com

Signature

Dave Peterson
ec35720@msn.com

Reply to this Message