Cannot Refer to Cell String Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve Drenker - 27 Jun 2006 19:46 GMT

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?

Reply to this Message

Bob Greenblatt - 27 Jun 2006 19:57 GMT

On 6/27/06 2:46 PM, in article C0C6CAFB.26238%sdrenkerNOSPAM@pacbell.net,

> I've got a new problem that cropped up in Excel 2004 on Mac PowerBook G4, OS
> X 10.3.9.
[quoted text clipped - 19 lines]
>
> Help! Anybody seen this before?

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.

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

Steve Drenker - 27 Jun 2006 20:02 GMT

> On 6/27/06 2:46 PM, in article C0C6CAFB.26238%sdrenkerNOSPAM@pacbell.net,
>
[quoted text clipped - 26 lines]
> the leading apostrophe so all formulas start with = as the first character.
> That ought to fix it. If not, let us know.

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

Reply to this Message

Bernard Rey - 27 Jun 2006 21:02 GMT

Steve Drenker :

> I've got a new problem that cropped up in Excel 2004 on Mac PowerBook G4, OS
> X 10.3.9.
[quoted text clipped - 19 lines]
>
> Help! Anybody seen this before?

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.

Signature

Bernard Rey - Toulouse / France
MVP - office:macintosh http://faq.office.macintosh.free.fr

Reply to this Message

Steve Drenker - 27 Jun 2006 21:13 GMT

> Steve Drenker :
>
[quoted text clipped - 31 lines]
> 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.

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

Reply to this Message

Mac Forum / Applications / Excel / June 2006

Cannot Refer to Cell String Data