In the second worksheet, I want my formula in cell I2 to reference cell I2
of the first worksheet. I want to copy all of the second worksheet, and paste
in the third worksheet, and in cell I2 of the third worksheet, I want the
pasted formula to use cell I2 of the second worksheet, not cell I2 of the
first worksheet. But when I try this, Excel enters in cell I2 of the third
worksheet the formula from cell I2 of the second worksheet ''as is'', not as
a relative reference: In cell I2 of worksheet Q2, I have the following
formula:

=H2+'Q1'!I2

and this is what I want. In cell I2 of worksheet Q3, a paste y;elds

=H2+'Q1'!I2

but this is not what I want. I want the paste to give me the following, so
that I do not have to edit the pasted formula:

=H2+'Q2'!I2

Copy the cell I2 in the second worksheet. Go to cell I2 in the third, and
from the edit menu, choos Paste Special -- Paste Link. Instead of the cell
contents (formula), it will paste a reference to the copied cell.

"Busy Beaver" wrote:

In the second worksheet, I want my formula in cell I2 to reference cell I2
of the first worksheet. I want to copy all of the second worksheet, and paste
in the third worksheet, and in cell I2 of the third worksheet, I want the
pasted formula to use cell I2 of the second worksheet, not cell I2 of the
first worksheet. But when I try this, Excel enters in cell I2 of the third
worksheet the formula from cell I2 of the second worksheet ''as is'', not as
a relative reference: In cell I2 of worksheet Q2, I have the following
formula:

=H2+'Q1'!I2

and this is what I want. In cell I2 of worksheet Q3, a paste y;elds

=H2+'Q1'!I2

but this is not what I want. I want the paste to give me the following, so
that I do not have to edit the pasted formula:

=H2+'Q2'!I2

John Walkenbach has a custom function that should help you.
http://www.j-walk.com/ss/excel/tips/tip63.htm


"Busy Beaver" wrote:

In the second worksheet, I want my formula in cell I2 to reference cell I2
of the first worksheet. I want to copy all of the second worksheet, and paste
in the third worksheet, and in cell I2 of the third worksheet, I want the
pasted formula to use cell I2 of the second worksheet, not cell I2 of the
first worksheet. But when I try this, Excel enters in cell I2 of the third
worksheet the formula from cell I2 of the second worksheet ''as is'', not as
a relative reference: In cell I2 of worksheet Q2, I have the following
formula:

=H2+'Q1'!I2

and this is what I want. In cell I2 of worksheet Q3, a paste y;elds

=H2+'Q1'!I2

but this is not what I want. I want the paste to give me the following, so
that I do not have to edit the pasted formula:

=H2+'Q2'!I2