Copying formulas between different versions into Excel 2007

[karenj]

When I copy/paste a formula from an old 97-2003 file (example: cell C1 reads
=A1+B1) to a new 2007 file, it's converted from a formula to a number. I
don't want to have to recreate all the formulas in the new 2007 file.
Here's what I've tried with no success:
- choose Paste Special (it treats it as if it were an object instead of a
formula)
- save the 97-2003 file as a 2007 version, then copy/paste into a new 2007
file
- save the new 2007 file down to a 97-2003 version, then copy/paste into the
97-2003 version
- save both files as the same versions, then copy/paste

The only thing that does work is to open the 97-2003 file and save it as a
2007 version - it leaves the formulas and allows me to change the data.
However, I have current files filled with data that I simply need to
copy/paste the formulas from that old file.

Please share your wisdom, oh great ones... thanks!

 

[jamescox]

It's not precisely clear what you mean about the Paste Special treating
it as if it were an object.

Did you try Paste Special Formulas?

Something else that should work is to copy the formula as text - ie,
select the cell, click and drag through the formula in the formula bar,
copy it, click off the formula in the formula bar to remove the
highlight on the formula and exit the formula edit mode in the old
workbook and then click on the destination cell in the 2007 workbook and
paste into the formula bar in the 2007 workbook.

If that seems to be working, you can, at the loss of cell formatting,
simplify things a bit by using find and replace in the old workbook to
replace equal signs (=) with a character string that makes the cell
contents not a formula (I tend to use >!= which seems to be a unique
character string in my workbooks), then copy and paste the cells from
the old workbook to the 2007 one. Note you don't have to do the text
copy step if you've done this.

Once all your formulas are in the 2007 workbook do a find and replace
of >!= with = and your formulas should be in place...