I want to pick 3000 data points from a large dataset. The data is placed in the cell, which coordinate constantly increase by number of 9. For instance the first data point of interest is in a cell A1, the second in A9, the third in A19. So how to make a formular to shift picking cell by 9 (something like that A1+9=A10, A10+9=A19 …)?

*From A1, 8 rows down to A9, 10 further rows down to A19. I assume you meant for the second cell to be A10, as indicated in your final “something like …”. Please double check your data intervals.*

If your formulas to pick data are in consecutive cells down a column, you can use OFFSET() and ROW(). Assuming your formulas are starting from row 1:

`=OFFSET(A1;(ROW()-1)*9;0)`

`=OFFSET($A$1;(ROW()-1)*9;0)`

- If your formulas start further down the sheet, subtract the starting row number from
`ROW()`

in the inner parenthesis. - If your formulas are spread horizontally, you need the
`COLUMN()`

function instead of`ROW()`

. - If your layout is not so straightforward, please provide detail.

better:

`=OFFSET(A$1;(ROW()-1)*9;0)`

better:

`=OFFSET(A$1;(ROW()-1)*9;0)`

Updated accordingly, and to also paste a sensible “anchor address” with horizontal distribution. Thanks!

Thanks a lot for the reply. Somehow it does not work, as it should. I will specify the problem. Lets say I have three data blocks:

The first in A1, …, A9 the 2nd in A10, …, A18, the 3rd in A19, …A27. I want to have the value from A1, A10 and A19 in one separate column like A1, A10 and A19 values located for instance in B column, raws 1, 2, 3. So the 1st data point I can write in B1: =A1, the 2nd (in B2) =`OFFSET(A$1;(RAW()-1)*9;0)`

, the 3rd (in B3) =`OFFSET(A$1;(RAW()-2)*9;0)`

?

No.

In B1: `=OFFSET(A$1;(ROW()-1)*9;0)`

*The offset will then be zero, so you will be referring to A1*

- Copy that formula down as far as you need.
*You should not need to edit the formula further.* - When the formula is pasted into in B2 it will evaluate to
`OFFSET(A1;(2-1)*9;0)`

=`OFFSET(A1;9;0)`

=`A10`

- In B3 it will evaluate to
`OFFSET(A1;(3-1)*9;0)`

=`OFFSET(A1;18;0)`

=`A19`

- …

Note the spelling. Function `ROW()`

, not `RAW()`

Thank a lot for your help. Now it works. The problem was that I did not copy the formula down, but tried to change (ROW()-1) to (ROW()-2) etc. The tricky part is that you do not see, how the number under ROW() function changes, when you copy a formula down. Thanks a lot once more for solving my problem.