# Relative addressing with INDIRECT function

1. ## 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?

--
John Uebersax

2. ## Re: 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?
>
>
> --
> John Uebersax

3. ## Re: Relative addressing with INDIRECT function

aidan.heritage@virgin.net 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

4. ## Re: 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" <jsuebersax@yahoo.com> wrote in message
> aidan.heritage@virgin.net 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
>

5. ## Re: 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:

> aidan.heritage@virgin.net 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
>
>

6. ## Re: 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

7. ## Re: Relative addressing with INDIRECT function

--
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
>
>

8. ## Re: Relative addressing with INDIRECT function

This appears to work. Thank you.

John

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1