Unable to change formula in excel sheet

[jabwylie]

I have following formula in sheet COUNTA(D6:D28), it works beautifully, the whole sheet is protected as it's for my boss and he's prone to bugger things up.

I need to change the range to finish on D27 now, easy peasy, I thought.

I unprotected the sheet, and changed the value.................it wont work.

The formula is displayed in the cell instead of the answer??

The answer is used in another sheet, but I even tried copying the sheet to a new workbook and changing the formula to no avail. I'm sure it must be something stupid, but I am a fafirly competent excel user and have never been unable to change a formula in a sheet???

Help please.

Ps , I cannot change ANY of the formulas in the sheet not just this one

 

[BBHRVS]

Hi;
If the formula is displayed in the cell, then go to the excel menu:
Tools/Options/Views/Formula
It should be clicked on.
Unclick to come back to "normal" view.
Are you now able to change the formula ?
BRgds

 

[jabwylie]

VIEW

When I went to that menu , view / windows options / formulas was not selected already?

 

[jabwylie]

PS

If I change the formula and it doesn't work, I can't even change it BACK to what it was manually. If I re-enter D28 manually it refuses to work, only undo makes the sheet work as normal?

 

[NBVC]

Does this do anything for you?


Do a Find/Replace and put an = sign in both Find and Replace fields, then Replace All.

 

[jabwylie]

NOPE

Did that , but again would not work, formula displayed in cell and all cells dependant on result ******

 

[just_jon]

Have you formatted that cell as Text?

Is there an = sign in front of what you posted?

 

[jabwylie]

WEIRD

Ok,
so I'm trying to get around this problem by changing the sheet so that I can leave the original formula alone and instead of entering the data I didnt want it to count withing the D6:D28 range I've inserted a new row and used that instead.

So all's going well, I need to use the value in the cell in another sheet, so put in formula which worked fine to get value into other sheet.

BUT

I couldn't copy the formula down on second sheet (due to layout) so I thought I'll just copy and paste it into the other two cells required and then manually alter the cell reference in the formula ......................

I'll be damned if it wouldn't have any of it ...............

Basically if you enter a virgin formula in a cell it's happy as can be, try to change it in any way at all and it wont have it.............???

I'm lost

 

[jabwylie]

JUST JON

Yeah there is an = sign in front of formula, cell was formated as text, but chnaged to general and din't make a jot of difference........

 

[just_jon]

Re: JUST JON

Yeah there is an = sign in front of formula, cell was formated as text, but chnaged to general and din't make a jot of difference........

Now that the format is General, re-confirm the formula.