Making cell reference absolute makes cell format text

Hi

Weird one here! I've been browsing the Excel forums for years and not seen
this.
One sheet is called Special One Off Deals, and in cell C2 is Aug 06 -
formatted as text.
In another sheet, any General cell, I type = and then navigate to C2 in
the other sheet and hit enter. The result is Aug 06 - formatted as text.
That's fine - and as a result the cell format changes to text (which I
didn't expect). I now edit the cell and change the reference to absolute (ie
='Special One Off Deals'!$C$2 ) and now the formula displays in the cell -
not the result. Why?
I have tried it on two different PCs and get the same each time. It's got me
beat!
Cheers.
Andy.

I think you've just missed some posts bg.

Excel likes to help.

Try this on a test worksheet.
Select A1 and hit ctrl-; (to put the date in the cell)
now select B1 and type: =a1

Notice that excel changed the format of B1 to match the format in A1.

Now format D1 as Text.
put ASDF in D1
put =D1 in E1
You see ASDF.

With E1 selected, hit the F2 key and then enter (to pretend that you're changing
the formula).

Excel has "helped" you by changing that cell's format to text.

I don't know of any way of changing this behavior.

I just select the cell, and reformat it to General (or whatever I wanted). I
hit F2 and then enter (to reenter that formula).

Sometimes this feature is nice, sometimes it ain't.

Andy wrote:

Hi

Weird one here! I've been browsing the Excel forums for years and not seen
this.
One sheet is called Special One Off Deals, and in cell C2 is Aug 06 -
formatted as text.
In another sheet, any General cell, I type = and then navigate to C2 in
the other sheet and hit enter. The result is Aug 06 - formatted as text.
That's fine - and as a result the cell format changes to text (which I
didn't expect). I now edit the cell and change the reference to absolute (ie
='Special One Off Deals'!$C$2 ) and now the formula displays in the cell -
not the result. Why?
I have tried it on two different PCs and get the same each time. It's got me
beat!
Cheers.
Andy.

--

Dave Peterson

Thanks for your reply, Dave. You're right - I must have missed some posts.
I've never experienced this 'feature' on Excel in 15 years of usage!

Cheers.
Andy.

"Dave Peterson" wrote in message
...
I think you've just missed some posts bg.

Excel likes to help.

Try this on a test worksheet.
Select A1 and hit ctrl-; (to put the date in the cell)
now select B1 and type: =a1

Notice that excel changed the format of B1 to match the format in A1.

Now format D1 as Text.
put ASDF in D1
put =D1 in E1
You see ASDF.

With E1 selected, hit the F2 key and then enter (to pretend that you're
changing
the formula).

Excel has "helped" you by changing that cell's format to text.

I don't know of any way of changing this behavior.

I just select the cell, and reformat it to General (or whatever I wanted).
I
hit F2 and then enter (to reenter that formula).

Sometimes this feature is nice, sometimes it ain't.

Andy wrote:

Hi

Weird one here! I've been browsing the Excel forums for years and not
seen
this.
One sheet is called Special One Off Deals, and in cell C2 is Aug 06 -
formatted as text.
In another sheet, any General cell, I type = and then navigate to C2 in
the other sheet and hit enter. The result is Aug 06 - formatted as text.
That's fine - and as a result the cell format changes to text (which I
didn't expect). I now edit the cell and change the reference to absolute
(ie
='Special One Off Deals'!$C$2 ) and now the formula displays in the
cell -
not the result. Why?
I have tried it on two different PCs and get the same each time. It's got
me
beat!
Cheers.
Andy.

--

Dave Peterson