Data lookup

[scott]

I am trying to find a formula that will allow me to pull data from a cell
that is contained in a set number of cells below the current cell. For
example, if I have data in cells A1:A30, I would like to have Cell B1 to
contain the data in A1, but cell B2 to contain the data in A10. Similarly, I
want cell B3 to pull the data in cell A20, and so forth.

Can anyone help?

Thanks!

Scott

 

[Glenn]

I am trying to find a formula that will allow me to pull data from a cell
that is contained in a set number of cells below the current cell. For
example, if I have data in cells A1:A30, I would like to have Cell B1 to
contain the data in A1, but cell B2 to contain the data in A10. Similarly, I
want cell B3 to pull the data in cell A20, and so forth.

Can anyone help?

Thanks!

Scott

B1 =A1

B2 =INDIRECT("A"&(ROW()-1)*10)

and copy down as needed.

 

[scott]

I do not understand why that formula works, maybe I am just referencing the
cells incorrectly. When I use that formula, the data is not returned from
every 10 cells; rather, the first is returned from the 34th cell, followed by
the 38th cell, then the 36th cell. See below for the data that I have, maybe
this will be more clear.

a b
1. 0.00% =a1
2. 0.00% =indirect("a"&(row()-1)*10) returns 0.08 (contained in
cell a34)
3. 0.01%
4. 0.01%
5. 0.01%
6. 0.01%
7. 0.02%
8. 0.02%
9. 0.02%
10. 0.02%
11. 0.02%
12. 0.03%
13. 0.03%
14. 0.03%
15. 0.03%
16. 0.04%
17. 0.04%
18. 0.04%
19. 0.04%
20. 0.04%
21. 0.05%
22. 0.05%
23. 0.05%
24. 0.05%
25. 0.06%
26. 0.06%
27. 0.06%

I have 42,000 rows worth of data, so that may change the formula i need. Do
you have any other suggestion?

Thanks,

Scott