cell keeps showing address of reference cell and not contents

[Homer321]

I have been pulling my hair out over this for a very long time:
On certain cells only:
I click “+” and use the arrows/mouse to select a cell because I want the contents of that cell to appear in this cell.
The cell shows the address of that cell, i.e. it shows “R34” instead of the number “7” which is the content of cell R34

It seems the problem is to do with the referred to cell, i.e. cell R34, because wherever I refer to that cell, it does the same thing, will not allow me to refer to that cell content. Despite the fact that cell R34 appears to be the same as all the other cells. It shows it’s content “7” seemingly correctly.

This seems to happen quite rarely, but when it does, that certain cell (R34 in this case) can never be fixed, no matter what I do.

[anon73440385]

Hello,

Exercise: Perform the following:

• Open an empty new document
• Right click an arbitrary cell (e.g,. A1)
• Format Cells -> Text (Format code @)
• Enter + and move to some reference (perform what you describe, e.g. F8)

→ Voila: You get the text +F8

Lesson: Formatting a cell as text before entering any data disables interpretation of the input and thus also disables interpreting + as beginning of a formula (one of the rare cases where format controls input). Or in other words: Everything is interpreted being text, if you format a cell as text (Format code @) in advance.

Resolution Remove the formatting as text before entering formula (you may use CTRL+M shortcut)

Hope that helps.

If the answer helped to solve your problem, please click the check mark () next to the answer.

[Homer321]

Thank you. Your Exercise worked as you describe, so I understand the problem. But the Resolution didn’t work for me - I go to the cell which incorrectly shows “R34”, I press ctrl M, and it now shows “34” instead of the “7” which is the content of cell R34.

[anon73440385]

I wrote Remove the formatting as text before entering formula. Obviously you tried my resolution while the cell still contains the text . So - remove the content (text), type CTRL+M and reenter your formula.

[erAck]

Or (without removing the formula text and thus not needing to re-enter it), press Ctrl+M and then edit the cell modifying its content, e.g. hit F2 and append a blank and remove it again (backspace) and press Enter.