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

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

--
Biff
Microsoft Excel MVP


"scott" wrote in message
...
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

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" wrote:

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

--
Biff
Microsoft Excel MVP


"scott" wrote in message
...
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

how do I reference the cell directly next to it?

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

--
Biff
Microsoft Excel MVP


"scott" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"scott" wrote in message
...
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

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" wrote:

how do I reference the cell directly next to it?

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

--
Biff
Microsoft Excel MVP


"scott" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"scott" wrote in message
...
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

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))


--
Biff
Microsoft Excel MVP


"scott" wrote in message
...
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" wrote:

how do I reference the cell directly next to it?

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

--
Biff
Microsoft Excel MVP


"scott" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"scott" wrote in message
...
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