+ Reply to Thread
Results 1 to 8 of 8

Relative addressing with INDIRECT function

  1. #1
    john
    Guest

    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


  2. #2
    aidan.heritage@virgin.net
    Guest

    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?
    >
    > Thanks in advance.
    >
    > --
    > John Uebersax



  3. #3
    john
    Guest

    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. #4
    Bob Phillips
    Guest

    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
    news:1149680562.890967.137630@u72g2000cwu.googlegroups.com...
    > 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. #5
    Tom Ogilvy
    Guest

    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. #6
    John Uebersax
    Guest

    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. #7
    Tom Ogilvy
    Guest

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


  8. #8
    John Uebersax
    Guest

    Re: Relative addressing with INDIRECT function

    This appears to work. Thank you.

    John


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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