 How to shift the cell coordinate by constant number?

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 …”.

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.

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.