+ Reply to Thread
Results 1 to 6 of 6

Linking to non-adjacent cells

  1. #1
    Registered User
    Join Date
    04-30-2004
    Posts
    15

    Question Linking to non-adjacent cells

    I have 2 workbooks. In Column A of workbook Source, I have the numbers 1 through 20 starting in A1. In the second workbook, Summary, I want to link to every third row in Source (for simplicity, these will be located starting in B1 going down). So in the end, Summary will look like: B1=1, B2=4, B3=7, etc.

    I can do this manually for each cell but want to find a way that if I manually link to a few of the cells and then copy down the formula, the rest will automatically be completed. When I remove the dollar signs from the linked cell's address and then copy down, I get a consistent but very weird result.

    If I manually link the first three cells in Summary I have: B1=1, B2=4 and B3=7 and the forumlas look like: ='[Source.xls]Sheet1'!B1 after I delete the dollar signs. If I highlight all three of these linked cells and copy down, I get the following results:
    B1=1, B2=4, B3=7, B4=4, B5=7, B6=10, B7=7, B8=10, B9=13, B10=10, B11=13, etc.

    There is a pattern but I don't understand why it is doing that. Is there a way to link to these non-adjacent cells quickly or do I have to do it manually. Thanks.

  2. #2
    Misha
    Guest

    Re: Linking to non-adjacent cells

    Hi mworth01,

    Assuming your Summary column starts from row 1, you can use this

    =INDIRECT("'Source'!"&ADDRESS(ROW()*3,COLUMN())).

    The nested ADDRESS function builds a cell reference as text in pieces,
    which allows you to manipulate the value returned. ROW() gives you the
    row from the Summary sheet, then multiplies it by 3 so you get every
    third one. The COLUMN() just gives you the column from the Summary
    sheet. If you need to, you can add, subtract, or multiply to get the
    right column reference.

    The INDIRECT just takes what's inside the parentheses and builds the
    reference back from the text. So you concatenate the pieces you need,
    the reference to Source worksheet (that's a double-quote, single quote,
    Source, single quote, exclamation mark, double quote) using & as the
    concatenator.

    Good luck, Hope this helps,

    Misha


  3. #3
    Bernie Deitrick
    Guest

    Re: Linking to non-adjacent cells

    In Summary, cell B1, use a formula like (change the file and sheet name as appropriate):

    =INDEX('[Source.xls]Source Sheet'!$A:$A,(ROW()-1)*3+1)

    and copy down.

    HTH,
    Bernie
    MS Excel MVP


    "mworth01" <mworth01.2276tz_1138204801.3838@excelforum-nospam.com> wrote in message
    news:mworth01.2276tz_1138204801.3838@excelforum-nospam.com...
    >
    > I have 2 workbooks. In Column A of workbook Source, I have the numbers
    > 1 through 20 starting in A1. In the second workbook, Summary, I want
    > to link to every third row in Source (for simplicity, these will be
    > located starting in B1 going down). So in the end, Summary will look
    > like: B1=1, B2=4, B3=7, etc.
    >
    > I can do this manually for each cell but want to find a way that if I
    > manually link to a few of the cells and then copy down the formula, the
    > rest will automatically be completed. When I remove the dollar signs
    > from the linked cell's address and then copy down, I get a consistent
    > but very weird result.
    >
    > If I manually link the first three cells in Summary I have: B1=1, B2=4
    > and B3=7 and the forumlas look like: ='[Source.xls]Sheet1'!B1 after I
    > delete the dollar signs. If I highlight all three of these linked
    > cells and copy down, I get the following results:
    > B1=1, B2=4, B3=7, B4=4, B5=7, B6=10, B7=7, B8=10, B9=13, B10=10,
    > B11=13, etc.
    >
    > There is a pattern but I don't understand why it is doing that. Is
    > there a way to link to these non-adjacent cells quickly or do I have to
    > do it manually. Thanks.
    >
    >
    > --
    > mworth01
    > ------------------------------------------------------------------------
    > mworth01's Profile: http://www.excelforum.com/member.php...fo&userid=8991
    > View this thread: http://www.excelforum.com/showthread...hreadid=504953
    >




  4. #4
    Registered User
    Join Date
    04-30-2004
    Posts
    15
    Bernie,

    I completely agree that for the data set I presented in my example your solution works. But it seems to be very limited in its scope and I'm trying to adjust it to my real data and can't seem to find a combination that works.

    Take my example but change the numbers from 1-20 to 5-100 (intervals of 5) and relocate the source data from A1:A20 to C4:C23. If I'm not mistaken, now you can't simply rely on the sheet's row number in your index...you need to figure out the row number within the array (meaning for the first data point, 5, ROW() would return a 4 whereas I need it to return a 1 since it's the first data point in my array). I've been trying to modify your formula but haven't had any success. Thanks in advance for any replies.

  5. #5
    Bernie Deitrick
    Guest

    Re: Linking to non-adjacent cells

    =INDEX('[Source.xls]Source Sheet'!$C$4:$C$23,(ROW()-ROW($??$???))*3+1)

    Replace the $??$??? with the address of the first cell where this formula is entered, such as $F$5

    HTH,
    Bernie
    MS Excel MVP


    "mworth01" <mworth01.22ibam_1138723801.8351@excelforum-nospam.com> wrote in message
    news:mworth01.22ibam_1138723801.8351@excelforum-nospam.com...
    >
    > Bernie,
    >
    > I completely agree that for the data set I presented in my example your
    > solution works. But it seems to be very limited in its scope and I'm
    > trying to adjust it to my real data and can't seem to find a
    > combination that works.
    >
    > Take my example but change the numbers from 1-20 to 5-100 (intervals of
    > 5) and relocate the source data from A1:A20 to C4:C23. If I'm not
    > mistaken, now you can't simply rely on the sheet's row number in your
    > index...you need to figure out the row number within the array (meaning
    > for the first data point, 5, ROW() would return a 4 whereas I need it
    > to return a 1 since it's the first data point in my array). I've been
    > trying to modify your formula but haven't had any success. Thanks in
    > advance for any replies.
    >
    >
    > --
    > mworth01
    > ------------------------------------------------------------------------
    > mworth01's Profile: http://www.excelforum.com/member.php...fo&userid=8991
    > View this thread: http://www.excelforum.com/showthread...hreadid=504953
    >




  6. #6
    Registered User
    Join Date
    04-30-2004
    Posts
    15

    Thanks!

    Bernie,

    That's perfect...thank you so much for your quick response.

    Mike

+ 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