data lookup

[scott]

I am looking for a formula that will allow me to tell a cell to return data
from a cell that is a set number of cells below the current cell in a
vertical array of data. For example, if I have data in cells A1:A100, I want
cell B1 to return cell A1, and cell B2 to return cell A10, and cell B3 to
return cell A20, etc.

Can anyone help?

Thanks!

Scott

 

[T. Valko]

Try this:

Entered in B1 and copied down as needed.

=INDEX(A$1:A$30,(ROWS(B$1:B1)-1)*10)

This is what you'll get:

B1 = A1
B2 = A10
B3 = A20
B4 = A30

 

[scott]

Thank you very much, this is exactly what I needed. I have over 45,000 rows
of data and this saved me a tremendous amount of time. Now, I have another
question:

Since I am able to reference the data that I need. how do I reference the
cell directly next to it? I believe this is a vlookup function, but I have
never used it before.

 

[T. Valko]

how do I reference the cell directly next to it?

You could use the same formula just change the column that it references.

 

[scott]

Ok, I should have figured that out before I asked that. Thank you though.
Now that I have the formula put into the worksheet, I double-clicked the
little black box in the cell to have the formula copy all the way down. The
problem is, since the formula is referencing blank cells after the last value
is referenced, I am getting values that return #ref!. Is there an "if" or
"or" statement that I can add to the formula that tells the fomula to stop
referencing cells once all the cells with numbers have been referenced?

 

[T. Valko]

One way:

If you're using Excel 2007:

=IFERROR(INDEX(A$1:A$30,(ROWS(B$1:B1)-1)*10),"")

This one will work in any version of Excel:

=IF(ISERROR(INDEX(A$1:A$30,(ROWS(B$1:B1)-1)*10)),"",INDEX(A$1:A$30,(ROWS(B$1:B1)-1)*10))