# 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.
• If your formulas are spread horizontally, you need the `COLUMN()` function instead of `ROW()`.

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.