I have several Excel workbooks that were originally created in Excel 2003 or
earlier versions. Once I installed Excel 2007 early this year, I've been
having problems with formulas that were working correctly suddenly stop
functioning.

First, a REF! error shows in the cells. The error message says that deleting
or moving a column has caused a problem. I then go in to the formula to put
in the correct referenced cells. The Results while I am in the formula window
show the values correctly, but when I close the formula window then only the
formula shows in the cells, not the resulting value. The only fix I have
found for this is to delete the entire column, insert a new column and put
the formula in again. This has been happening all year on occasion, but I've
always been able to finally fix the problem.

Tonight, however, deleting the column and inserting a new one did not help.
While building the formula the Results are correct, but once I finish the
formula, only the formula shows up in the cells, not the value.

Can anyone help me with this?

Always post your formula.

But I think what happens is your cells were formatted as text when you entered the formulas.
Formatting as Number (or something else) afterwards doesn't help; Format as General AND re-enter the formula (F2, ENTER).
For columns full of formulas use Find and Replace, and replace = by =. That has the same effect as-entering the formula.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"cyndiwise notsowise" wrote in message
...
|I have several Excel workbooks that were originally created in Excel 2003 or
| earlier versions. Once I installed Excel 2007 early this year, I've been
| having problems with formulas that were working correctly suddenly stop
| functioning.
|
| First, a REF! error shows in the cells. The error message says that deleting
| or moving a column has caused a problem. I then go in to the formula to put
| in the correct referenced cells. The Results while I am in the formula window
| show the values correctly, but when I close the formula window then only the
| formula shows in the cells, not the resulting value. The only fix I have
| found for this is to delete the entire column, insert a new column and put
| the formula in again. This has been happening all year on occasion, but I've
| always been able to finally fix the problem.
|
| Tonight, however, deleting the column and inserting a new one did not help.
| While building the formula the Results are correct, but once I finish the
| formula, only the formula shows up in the cells, not the value.
|
| Can anyone help me with this?

Thanks, Niek - you're a life-saver!!

I'm not sure how all of the cells became formatted as text - I know I didn't
format them that way when I set them up. I had not yet had a chance to try
your solution when I began working in one of the bigger spreadsheets tonight.
All of a sudden, after I had put in new information in several non-formula
cells, the ENTIRE spreadsheet just quit calculating any of the formulas. This
spreadsheet is linked to another spreadsheet and when the formulas quit
working, EVERY cell in the spreadsheet showed N/A or REF instead of the
information it had previously shown correctly.

I selected the entire spreadsheet and changed the number format to
"General", even though this messed up some cells that had dates, percentages,
and prices in them. (I went back later and changed the columns with dates,
percentages, and prices to "Number"). Then, I used Find & Replace to replace
EVERY "=" with "=". This took about an hour for Excel to accomplish, but once
it was finished (there were over 300,000 replacements!) the whole spreadsheet
was showing all the correct information again. "Whew!"

Thanks again for posting the exact solution I needed. BTW - I didn't post my
formula with my original question because the problem was happening with a
different kinds of formulas, and I could not have possibly posted all the
various formulas I am using in this spreadsheet.

"Niek Otten" wrote:

Always post your formula.

But I think what happens is your cells were formatted as text when you entered the formulas.
Formatting as Number (or something else) afterwards doesn't help; Format as General AND re-enter the formula (F2, ENTER).
For columns full of formulas use Find and Replace, and replace = by =. That has the same effect as-entering the formula.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"cyndiwise notsowise" wrote in message
...
|I have several Excel workbooks that were originally created in Excel 2003 or
| earlier versions. Once I installed Excel 2007 early this year, I've been
| having problems with formulas that were working correctly suddenly stop
| functioning.
|
| First, a REF! error shows in the cells. The error message says that deleting
| or moving a column has caused a problem. I then go in to the formula to put
| in the correct referenced cells. The Results while I am in the formula window
| show the values correctly, but when I close the formula window then only the
| formula shows in the cells, not the resulting value. The only fix I have
| found for this is to delete the entire column, insert a new column and put
| the formula in again. This has been happening all year on occasion, but I've
| always been able to finally fix the problem.
|
| Tonight, however, deleting the column and inserting a new one did not help.
| While building the formula the Results are correct, but once I finish the
| formula, only the formula shows up in the cells, not the value.
|
| Can anyone help me with this?