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