Inter sheet formula won't format properly

  • Thread starter Thread starter Alec
  • Start date Start date
A

Alec

Hi
MS Excel 2003.

I am writing a formula with a reference to a cell in another sheet, and
Excel is behaving oddly. Sometimes it will give me the result, ie what
is in the target cell, but sometimes it gives me the formula.

The formula I am writing is simply

='2'!A22

thereby trying to get cell A22 from the sheet called 2.

Why is Excel not always just giving me what's in the target cell?
Sometimes all I can see is ='2'!A22. It's driving me nuts.

I have looked at all the formatting options with no luck; I have toggled
(using Ctrl`) the formula view with the normal view, but no luck.

Any ideas?
Cheers
Alec
 
Sound like the cell with the formula in is formatted as text.
Brian
Click to expand...

Right click your cell, change the number type to 'General' in the
Number tab of the Format Cells dialog. Once you've done this try
typing in the formula again and it should work just fine.

Hope this helps,
Matt Richardson
http://teachr.blogspot.com
 
Matt said:
Right click your cell, change the number type to 'General' in the
Number tab of the Format Cells dialog. Once you've done this try
typing in the formula again and it should work just fine.

Hope this helps,
Matt Richardson
http://teachr.blogspot.com
Click to expand...

OK, thanks, this worked, BUT it seems that Excel is automatically
changing the format of a cell back to text when I edit it. Why? Each
time I edit the formula in the cell, it changed the format from General
to text, so each time I have to change it back. I don't get it.
Cheers
Alec
 
Saved from a previous post.

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.
 
Dave said:
Saved from a previous post.

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.
Click to expand...

Definitely not a help in what I am doing. PITA.

Anybody know how to turn this off?
Alec
 
Back
Top