copy equations between spreadsheets

[Greenbeard]

This should be simple...

I have equations in one spreadsheet that reference cells in other worksheets
within that spreadsheet. I'm looking to copy these equations to another
spreadsheet and retain the relative reference to the named worksheet (i.e.
there's a worksheet in the new file with a name that matches the worksheet
from the previous file). However, when I do the copy / paste, I the equation
references the worksheet in the previous spreadsheet.

To clarify: say I'm copying the equation "=Sheet1!a1" from OldFile.xls to
NewFile.xls but in NewFile, the equation becomes "=[OldFile]Sheet1!a1" when I
just want the original equation. Any suggestions to avoid manually editing
everything?

thanks

 

[Ragdyer]

"UNformulate" the formulas by replacing the = sign with something else,
making them text, then copy and paste to your new location (worksheet), then
put the = signs back to make them formulas again.

Select the formulas to be copied, then:
<Edit> <Replace>,
In the "Find What" box enter the =sign,
In the "Replace With" box enter
^^
Then click <Replace All>

While the "formulas" are *still* selected, right click in the selection and
choose "Copy",
Navigate to your new location and right click in the starting cell and
choose "Paste",

Then, while they're all *still* selected, do that <Edit> <Replace> again,
And reverse what you did before,
In the "Find What" box enter ^^,
In the "Replace With" box enter
=
Then click <Replace All>

And you should be done!

 

[Joel]

Use the replace command on the Edit menu. replace everything between the []
brackets including the brackets. You only have to do this once after making
all the copies. If you are doing this on different worksheets, then select
in the replace form options. Change sheet to workbook.