Cell only shows link in text, not contents of reference cell

Some cells on the worksheet will show the information in the referenced cell.
Others only show the statement. For example, if I enter ='log'!K5, that's
exactly what appears in that cell, not the contents of the referenced cell.

John

Since you say that only *some* cells exhibit this problem, I would guess
that the "bad" cells were probably formatted as text,*before* you entered
the formulas.

If that's the case, you must change the formatting *and* re-register the
formula in order to obtain a valid XL formula.

Try this:
Select a "bad" cell,
Then
<Ctrl> <Shift> < ~ >
Then
<F2>
Then
<Enter>

What you did here, was
Keyboard shortcut to "General" format
Enter "Edit" mode
Re-register formula.

If you have a great many of these problem cells (formulas), you can select
them all,
Change the format en masse,
<Ctrl> <Shift> < ~ >

Re-register en masse using,
<Edit> <Replace>

Find what
=
Replace with
=
<Replace All>

--

HTH,

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

"Jay Mac" <[email protected]> wrote in message
news:[email protected]...
Some cells on the worksheet will show the information in the referenced
cell.
Others only show the statement. For example, if I enter ='log'!K5, that's
exactly what appears in that cell, not the contents of the referenced cell.

John

Try hitting F2 then Enter.

Tools, Options, Calculation, calculation: automatic

Does F9 fix it
Does CTRL+ALT+F9 fix it
or Ctrl+Alt+Shift+F9 for Excel 2002 will rebuild all dependency trees

check format of cells, if it is text that would do it
select cell, format, cells, (see what you have for format if not General)

If you have a space in front of = sign you will just see "formula"
check the length of cell again what you see =LEN(A1)
You might try TRIMALL macro in
http://www.mvps.org/dmcritchie/excel/join.htm
which is useful for a lot of screwups, including
- changing between text and number or number and text without reentering,
- removing extraneous spaces or Char(160) which is the Required Blank or non-breaking space
especially if source was HTML which has &nbsp;

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jay Mac" <[email protected]> wrote in message news:[email protected]...
> Some cells on the worksheet will show the information in the referenced cell.
> Others only show the statement. For example, if I enter ='log'!K5, that's
> exactly what appears in that cell, not the contents of the referenced cell.
>
> John

Thanks, David and RD. I appreciate the help.

Jay Mac

"David McRitchie" wrote:

> Try hitting F2 then Enter.
>
> Tools, Options, Calculation, calculation: automatic
>
> Does F9 fix it
> Does CTRL+ALT+F9 fix it
> or Ctrl+Alt+Shift+F9 for Excel 2002 will rebuild all dependency trees
>
> check format of cells, if it is text that would do it
> select cell, format, cells, (see what you have for format if not General)
>
> If you have a space in front of = sign you will just see "formula"
> check the length of cell again what you see =LEN(A1)
> You might try TRIMALL macro in
> http://www.mvps.org/dmcritchie/excel/join.htm
> which is useful for a lot of screwups, including
> - changing between text and number or number and text without reentering,
> - removing extraneous spaces or Char(160) which is the Required Blank or non-breaking space
> especially if source was HTML which has
>
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Jay Mac" <[email protected]> wrote in message news:[email protected]...
> > Some cells on the worksheet will show the information in the referenced cell.
> > Others only show the statement. For example, if I enter ='log'!K5, that's
> > exactly what appears in that cell, not the contents of the referenced cell.
> >
> > John
>
>
>

Good, of course knowing what the actual cause and solution was would
be even better feedback. But I know that each of the solutions have been
used to solve a similar sounding problem. Sometimes in telling what the cause
and solution turned out to be you get still more suggestions on avoiding the
problem in the first place.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jay Mac" <[email protected]> wrote in message news:[email protected]...
> Thanks, David and RD. I appreciate the help.
>
> Jay Mac
>
> "David McRitchie" wrote:
>
> > Try hitting F2 then Enter.
> >
> > Tools, Options, Calculation, calculation: automatic
> >
> > Does F9 fix it
> > Does CTRL+ALT+F9 fix it
> > or Ctrl+Alt+Shift+F9 for Excel 2002 will rebuild all dependency trees
> >
> > check format of cells, if it is text that would do it
> > select cell, format, cells, (see what you have for format if not General)
> >
> > If you have a space in front of = sign you will just see "formula"
> > check the length of cell again what you see =LEN(A1)
> > You might try TRIMALL macro in
> > http://www.mvps.org/dmcritchie/excel/join.htm
> > which is useful for a lot of screwups, including
> > - changing between text and number or number and text without reentering,
> > - removing extraneous spaces or Char(160) which is the Required Blank or non-breaking space
> > especially if source was HTML which has
> >
> > ---
> > HTH,
> > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
> >
> > "Jay Mac" <[email protected]> wrote in message news:[email protected]...
> > > Some cells on the worksheet will show the information in the referenced cell.
> > > Others only show the statement. For example, if I enter ='log'!K5, that's
> > > exactly what appears in that cell, not the contents of the referenced cell.
> > >
> > > John
> >
> >
> >