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

"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!
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Greenbeard" wrote in message
...
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

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.


"Greenbeard" wrote:

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