Relative addressing with INDIRECT function
 

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

Relative addressing with INDIRECT function
 

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

Relative addressing with INDIRECT function
 

Relative addressing with INDIRECT function
 

=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

Relative addressing with INDIRECT function
 

=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

Relative addressing with INDIRECT function
 

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

Relative addressing with INDIRECT function
 

=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

Relative addressing with INDIRECT function
 

This appears to work. Thank you.

John