Formulas automatically changing to values

[Obbsie]

I have a workbook that has started to do something odd. On 1 tab only, every formula I attempt to enter now automatically calculates the value and replaces the formula with said value. It only does this on 1 of the 2 tabs. It does not matter what formula I use nor which cell I attempt to update. After entering the formula, at the point I press "Enter" the cell remains active (cursor does not move to next cell) and replaces the formula with the calculated value.

As mentioned, cells retain formulas in the 2nd tab on the workbook. I have attempted to enter the formulas on the 2nd tab and paste them to the first, but it also automatically pastes the value only. DRIVING ME INSANE! I am using Excel 2003. I created the workbook over 2 years ago and have not altered any settings for the workbook or Excel in general and have enterd new formulas many times before.

 

[bensonsearch]

sounds like a setting is making the formulas calculate and show value only, have you had a look in the settings under formulas?

 

[Obbsie]

Thanks, but I wasn't able to find any setting in options that looks like it would be causing it. The strangest part is the fact it is only applying to 1 tab in 1 workbook.

 

[konew1]

Are these formula getting data from other cells or just a calculation of data entered within the cell. When I used xl2003 I sometimes entered (say) +100*1.15 because I wanted to keek track of the 2 components, but didn't want to clutter the sheet with them. Sometimes (not always) excel would convert it to 115. If I entered +123*(1/12) I could get =123*0.08333.
I think my problem was that I started with "+" sign. When I started being careful to start with equals "=" sign it seemed to go away

 

[bensonsearch]

Hi,
Im not sure where it is in 2003 but 2007 under advanced I found a rule that applies by sheet (tab)

the option is show formulas in cells instead of their calculated results. if ticked will do as you described, so needs to be unticked.

There would be a similar option in 2003

 

[Obbsie]

Thanks, but it is happening with any formula. =TODAY(), Vlookups, SUM, CONCATENATE. It's not an issue with a specific formula, but with a setting somewhere. Cheers.

 

[Obbsie]

Hi,
Im not sure where it is in 2003 but 2007 under advanced I found a rule that applies by sheet (tab)

the option is show formulas in cells instead of their calculated results. if ticked will do as you described, so needs to be unticked.

There would be a similar option in 2003

Thanks, but again there appears to be no such area in any "Options" tab. Keep 'em coming though

 

[bensonsearch]

in 2007 , its different but i go to the office button then excel options then advanced

 

[RoryA]

It sounds to me as though you have Worksheet_change code behind the sheet. If you right-click the worksheet tab and choose View Code, does anything appear?

 

[Obbsie]

It sounds to me as though you have Worksheet_change code behind the sheet. If you right-click the worksheet tab and choose View Code, does anything appear?

You are indeed a god. Found the below in the code:

Private Sub Worksheet_Change(ByVal Target As Range)
'retain formatting when a cell is copied over
Dim myValue
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True

Removed it and it works fine. I didn't write it and if I find the person that did - watch out. That's the problem with letting workmates use your creations.

Thanks to all that replied