Worksheet name in copied formulas

[Roberto]

Hi,

I have a problem trying to copy formulas from one
worksheet to another.
In the first worksheet I have formulas that make reference
to other cells. I want to copy one of these formulas to a
new worksheet. When I do so, what I get is a reference to
the cell from the original worksheet. Which I want is the
entire formula contained in the original cell.

Example:
Worksheet 1 (name NM)
A1: 1
A2:2
A3:3 (=A1+A2)

Worksheet 2
A1: =NM!A3 (copied from Worksheet 1 cell A3).... the
result I want is =NM!A1+NM!A2

I tried to include the name of the original worksheet (as
it is possible using the F4 key to get the $ sign for the
absolute reference) on each one of the formulas, but I
don't find a way to do so.

Thanks...

 

[Dave Peterson]

This seems to work if there are no other formulas refering to those formulas.
But it takes a couple of steps.

First copy the formula somewhere else on the same sheet (make sure you don't
paste it into a cell that causes #Ref! errors).

Now, instead of Edit|copy, use edit|cut and paste into the other worksheet. The
names come with the formula.

Then back to the first worksheet and copy|paste the "copied" formula.

Finally, clean up that helper cell.

===

But if you had other formulas refering to that cell with the formula, it now
points at the second sheet.