Cell content VS formula

[Bob Mignault]

Hi,

In a worksheet, I enter the formula: =Sheet1!A$127 and click Enter. The
result is that the contents of Sheet 1, Cell A127 appear.

However, when I change 127 to read 128 on the fx bar and click Enter, the
cell now displays the formula: =Sheet1!A$128 instead of the contents of
Sheet 1, Cell A128.

How can I display the contents of the cell on Sheet 1 instead of the
formula?

Thanks.

Bob Mignault

 

[Niek Otten]

In the meantime, you have changed the format of the cell to Text. Probably
without being aware.
Format the cell as General and re-enter the formula (F2, ENTER)

 

[Gord Dibben]

Bob

You or Excel are somehow changing the cell format to text or you are adding an
extra space at the beginning of the formula.

Re-format to General then hit F2 and ENTER

Does that clear it up?


Gord Dibben Excel MVP

 

[Pete]

The cell is probably formatted as text. Check this with Format | Cells,
and change as appropriate. You will need to edit the cell to force
Excel to accept the change.

Pete

 

[Bob Mignault]

Thanks Gord and other repliers.

Originally the cell into which I entered the formula was formatted General.

What happened was that when the contents of cell A127 on Sheet 1 appear in
the cell, Excel changed the formatting to Text because the contents of A127
was text.

What I have to do is re-format this cell to General. Now if I change 127 to
read 128, the contents of cell A128 appear.

Thanks,

Bob

 

[David McRitchie]

Hi Bob,
Thought I replied to the thread, and I also believe I referred another thread
to this one so I'd better include those other possibilities.

Sounds link in the meantime you either
- changed the format of the cell to Text
- turned of calculation (tools, option, calculation, automatic/manual)
- hit ctrl+` toggling into the formula view
- introduced a space or other character into the formula

and I should have included F2 then Enter that the others had.suggested
to test if a formatting change fixes or not. Since a change from number
to text -or- for text to number does not become effective until data is reentered.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
has additional information on checking what kind of data you have.

I just installed Excel 2002 so had never heard of the choice source
or target formatting. When does that actually come up and how
do you set it temporarily/permanently because I don't see it now (it was annoying).




---,
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