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.

Hi mworth01,

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

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

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
>

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

=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
>
>
> --
> mworth01
> ------------------------------------------------------------------------
> mworth01's Profile: http://www.excelforum.com/member.php...fo&userid=8991
>

6. ## Thanks!

Bernie,

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

Mike

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