Solved Why is excel 2010 not keeping my formulas?

October 26, 2017 at 05:46:18
Specs: Windows 7
I am using excel 2010 and have a macro enabled worksheet that has been through a number of changes. The formulas presently in the worksheet work fine but one had an error. When I change the formula, the result of the formula properly enters into the cell but excel is then taking the formula result as the cell value and not keeping the formula. I've copied and pasted, typed it in, cut and pasted and all result in the same error. Any ideas?

See More: Why is excel 2010 not keeping my formulas?

Reply ↓  Report •

✔ Best Answer
October 30, 2017 at 07:10:49
Our IT department rolled some update out over the weekend and now the excel sheet is working fine....not sure what the change was but now things appear to be working as expected.


#1
October 26, 2017 at 06:07:06
This statement doesn't make sense:

When I change the formula, the result of the formula properly enters into the cell but excel is then taking the formula result as the cell value and not keeping the formula.

I assume you mean "When I enter the formula..." You can't change the formula if Excel has replaced it with a value.

If that assumption is correct, then let's talk about the macro you mentioned. We are going to need to know more about that. Just as an example, this macro would do exactly what you are describing for any formula entered in A1. Note: This is just one of many, many examples of how a macro could be causing what you are experiencing.


Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$A$1" Then
   Target = Target.Value
  End If
End Sub

As soon as you enter the formula in A1, (e.g. =SUM(B1,C1) Excel will calculate the SUM first and then the Worksheet_Change code will fire and the macro will replace the formula with the result.

BTW...if something like that is happening, it is not an "error". Excel is simply doing what the VBA code is telling it to do.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#2
October 27, 2017 at 05:43:07
I'd be glad to send you the actual sheet. The macro is a form to "reset" certain fields back to a predetermined status and that's it.

The formula's involved are located in cells not part part of the macro in anyway....at least none that i can see.

for example. The macro would reset cell A1 to "PICK FROM LIST" which is a data validation list item.
in Cell B1, if I insert a formula such as =2+2 the cell shows the result of 4 and the formula bar also only shows a 4. In fact, I've tried in multiple cells and that seems to be consistent.

However, in Cell C1 I have an equation that was built when the form was originally created and it still has the equation set in the formula bar and it still operates. When I try to amend the formula I lose the formula entirely and again excel keeps only the result of the equation input.


Reply ↓  Report •

#3
October 27, 2017 at 07:31:26
I sent you a PM with an email address. Send the workbook and I'll take a look at it, but it won't be until this evening or tomorrow, EST.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
October 30, 2017 at 07:10:49
✔ Best Answer
Our IT department rolled some update out over the weekend and now the excel sheet is working fine....not sure what the change was but now things appear to be working as expected.

Reply ↓  Report •

#5
October 30, 2017 at 07:11:13
Thanks for trying to help me solve this....greatly appreciated.

Reply ↓  Report •

Ask Question