Cannot Refer to Cell String Data

S

Steve Drenker

I've got a new problem that cropped up in Excel 2004 on Mac PowerBook G4, OS
X 10.3.9.

In some spreadsheets, a reference in one cell to another cell's text returns
the formula, not the text in the other cell. In other words:

A1 contains "Macintosh"
A2 formula is '=a1'

Cell A2 should have "Macintosh". Instead I see '=a1'.

Preference / View / Window Options / Show Formulas is NOT checked.

The lower case 'a1' is not converted to upper case as is normal with a
formula.

Interestingly, some cells on the sheet work fine if I start off with "="
then click another cell. If I modify the resulting formula, the weird
behavior starts again. In other words, if I'm in G1, type "=", click in A1 I
get "Macintosh". If I then edit the formula to '=A1 & " used to work" ' I
then get the formula.

Help! Anybody seen this before?
 
B

Bob Greenblatt

I've got a new problem that cropped up in Excel 2004 on Mac PowerBook G4, OS
X 10.3.9.

In some spreadsheets, a reference in one cell to another cell's text returns
the formula, not the text in the other cell. In other words:

A1 contains "Macintosh"
A2 formula is '=a1'

Cell A2 should have "Macintosh". Instead I see '=a1'.

Preference / View / Window Options / Show Formulas is NOT checked.

The lower case 'a1' is not converted to upper case as is normal with a
formula.

Interestingly, some cells on the sheet work fine if I start off with "="
then click another cell. If I modify the resulting formula, the weird
behavior starts again. In other words, if I'm in G1, type "=", click in A1 I
get "Macintosh". If I then edit the formula to '=A1 & " used to work" ' I
then get the formula.

Help! Anybody seen this before?
Click to expand...
Do the offending cells really have an apostrophe as the first character? If
so, Excel treats the cell as text - just the symptoms you are seeing. Remove
the leading apostrophe so all formulas start with = as the first character.
That ought to fix it. If not, let us know.
 
S

Steve Drenker

Do the offending cells really have an apostrophe as the first character? If
so, Excel treats the cell as text - just the symptoms you are seeing. Remove
the leading apostrophe so all formulas start with = as the first character.
That ought to fix it. If not, let us know.
Click to expand...

Nope, no initial apostrophe. I just used that in the quoted text. Just an
=a1 & "abc" (for example). I'm aware that Excel uses a leading apostrophe to
denote literal text, not a formula.

Steve
 
B

Bernard Rey

Steve Drenker :
I've got a new problem that cropped up in Excel 2004 on Mac PowerBook G4, OS
X 10.3.9.

In some spreadsheets, a reference in one cell to another cell's text returns
the formula, not the text in the other cell. In other words:

A1 contains "Macintosh"
A2 formula is '=a1'

Cell A2 should have "Macintosh". Instead I see '=a1'.

Preference / View / Window Options / Show Formulas is NOT checked.

The lower case 'a1' is not converted to upper case as is normal with a
formula.

Interestingly, some cells on the sheet work fine if I start off with "="
then click another cell. If I modify the resulting formula, the weird
behavior starts again. In other words, if I'm in G1, type "=", click in A1 I
get "Macintosh". If I then edit the formula to '=A1 & " used to work" ' I
then get the formula.

Help! Anybody seen this before?
Click to expand...

Make sure your cell is formatted to anything you need but "TEXT" (from the
"Format" > "Cells" item) Once the cell is formatted as required, edit cell
A2 (double-click on the cell will be OK) and then press the Enter key.
Things should turn normal.

In fact, when a cell is formatted to "TEXT", anything you enter in the cell
will be considered as plain text and thus won't be treated like you may
expect. And once you changed the cell format (let's say to "Standard"), you
still have to edit the cell in for the change to be effective.
 
S

Steve Drenker

Steve Drenker :


Make sure your cell is formatted to anything you need but "TEXT" (from the
"Format" > "Cells" item) Once the cell is formatted as required, edit cell
A2 (double-click on the cell will be OK) and then press the Enter key.
Things should turn normal.

In fact, when a cell is formatted to "TEXT", anything you enter in the cell
will be considered as plain text and thus won't be treated like you may
expect. And once you changed the cell format (let's say to "Standard"), you
still have to edit the cell in for the change to be effective.
Click to expand...
Thanks, Bernard. I got things working by restarting the machine, making sure
my pasted-in data was in pure ASCII by using TextWrangler, and using Paste
Special / Text. Things were working after that.

I had checked formatting, but didn't see "TEXT" selected, but I had a large
selection and perhaps there were multiple formats. I'll remember that for
the future.

Steve
 
Top