Excel 2003 - Cant change formulas! 1

[dsmith910]

Hi I have just switched to Excel 2003 and find that for some reason once I have typed a formula into a cell I can't seem to change it. When I alter the formula Excel then just displays the formula itself rather than the solution. I figure I must have one of the settings wrong but cant figure out which one.
My latest formula =IF(ISNA(VLOOKUP(F37,transfer!$A$2:$H$2090,2,FALSE)),0,VLOOKUP(F37,transfer!$A$2:$H$2090,2,FALSE)) works fine but I should have started the array at $A$1 not $A$2! I have tried to alter the formula itself - I just get a display of the formula not the solution. I tried moving the array down one line so that line 1 became line 2 - naturally that changed all the formulas using that array and nothing worked. I have tried deleting the contents of the cell and retyping or copying/extending it. Also deleting the cell in its entirety, then reinserting a new cell then copy/extending for the previous cell - same thing happens!! Definitely getting frustrated. Can't remember this ever happening to me on 2000 that's why I think there is a setting wrong. Anyone able to help???

Thanks
Doreen

 

[DirkStruan]

Under tools/options/view then windows options (last section), do you have "Formulas" checked? Note that this should have made every formula show only the formula text rather than the result, so if you have a mix of both it isn't this.

If not, is the cell formatted to Text rather than general? If so reset to general and then F2 and Return on the cell.

D

 

[dsmith910]

Hi - many thanks. "Formulas" was not checked however the cell was formatted to text. Have changed it to general and that seems to work. I got out of the 'mess' yesterday by creating a new sheet and re-creating the thing again being careful to get the array right first time - laborious but I needed the sheet to work. Redoing everything is definitely not usually an option so your help is much appreciated.

Doreen