Linking to non-adjacent cells

[mworth01]

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

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

If I manually link the first three cells in Summary I have: B1=1, B2=
and B3=7 and the forumlas look like: ='[Source.xls]Sheet1'!B1 after
delete the dollar signs. If I highlight all three of these linke
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. I
there a way to link to these non-adjacent cells quickly or do I have t
do it manually. Thanks

 

[Misha]

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

 

[Bernie Deitrick]

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]

Bernie,

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

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

 

[Bernie Deitrick]

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

Bernie,

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

Mik