Hi group,

Suppose I want to copy this formula:

=A1*INDIRECT("Sheet2!a1")

To the next cell down, such that the new cell get's the formula:

=A2*INDIRECT("Sheet2!a2")

If I use the ordinary copy command, the new cell gets the formula:

=A2*INDIRECT("Sheet2!a1")

which is not what I want.

I know there is a way to do this, because I've done it before--but it
was a while back.

Can someone please refresh my memory?

Thanks in advance.

--
John Uebersax

Assuming that sheet2!a1 contains the cell reference, then the formula
is wrong - it should be

=A1*INDIRECT(Sheet2!a1)

which will then copy correctly (it's the quotes that cause the
problem!)

if it's sheet2!a1 you actually want to refer to then you don't need the
indirect at all.

john wrote:
Hi group,

Suppose I want to copy this formula:

=A1*INDIRECT("Sheet2!a1")

To the next cell down, such that the new cell get's the formula:

=A2*INDIRECT("Sheet2!a2")

If I use the ordinary copy command, the new cell gets the formula:

=A2*INDIRECT("Sheet2!a1")

which is not what I want.

I know there is a way to do this, because I've done it before--but it
was a while back.

Can someone please refresh my memory?

Thanks in advance.

--
John Uebersax

wrote:
Assuming that sheet2!a1 contains the cell reference, then the formula
is wrong - it should be

=A1*INDIRECT(Sheet2!a1)

No, I meant the question exactly as I asked it.

sheet2!a1 contains a value, not a formula or cell reference. The
purpose of the INDIRECT function here is to let me rearrange cells in
sheet2 without affecting formulas in sheet1.

In any case, removing the quotes doesn't work.

--
John Uebersax

=A1*INDIRECT("Sheet2!A"&ROW(A1))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"john" wrote in message
oups.com...
wrote:
Assuming that sheet2!a1 contains the cell reference, then the formula
is wrong - it should be

=A1*INDIRECT(Sheet2!a1)

No, I meant the question exactly as I asked it.

sheet2!a1 contains a value, not a formula or cell reference. The
purpose of the INDIRECT function here is to let me rearrange cells in
sheet2 without affecting formulas in sheet1.

In any case, removing the quotes doesn't work.

--
John Uebersax

=A1*Indirect("Sheet2!a" & row(A1))

would be one way. This wouldn't be affected by changes on Sheet2, but could
be affected by changes on the sheet with the formula.

--
Regards,
Tom Ogilvy


"john" wrote:

wrote:
Assuming that sheet2!a1 contains the cell reference, then the formula
is wrong - it should be

=A1*INDIRECT(Sheet2!a1)

No, I meant the question exactly as I asked it.

sheet2!a1 contains a value, not a formula or cell reference. The
purpose of the INDIRECT function here is to let me rearrange cells in
sheet2 without affecting formulas in sheet1.

In any case, removing the quotes doesn't work.

--
John Uebersax

Thanks Bob and Tom.

This seems to work for copying cells within the same column (which I
recognize was how I posed the question).

But what about also copying the formula

=A1*INDIRECT("Sheet2!a1")

to the next column, or to a 10x10 block of cells?

--
John

=a1*INDIRECT("Sheet2!"&ADDRESS(ROW(A1),COLUMN(A1)) )

--
Regards,
Tom Ogilvy


"John Uebersax" wrote:

Thanks Bob and Tom.

This seems to work for copying cells within the same column (which I
recognize was how I posed the question).

But what about also copying the formula

=A1*INDIRECT("Sheet2!a1")

to the next column, or to a 10x10 block of cells?

--
John

This appears to work. Thank you.

John