Autofill formula to return data from every tenth row

Question

In Excel 2007, how do I autofill a function of =OFFSET(C2,10,0) to end up as:

=OFFSET(C2,10,0)
=OFFSET(C12,10,0)
=OFFSET(C22,10,0)

etc.

as a pattern of referencing every 10th cell in the same row.

instead of this result:

=OFFSET(C2,10,0)
=OFFSET(C3,10,0)
=OFFSET(C4,10,0)

If have also tried:

=OFFSET(C2,10,0)
=OFFSET(C12,10,0)

But the result when dragging two selected cells ends up like this:

=OFFSET(C4,10,0)
=OFFSET(C14,10,0)

on the 3rd and 4th cell when autofilling.

There are 30,000 records to this data so simply deleting unwanted results would not be time-efficient.

Answer 1

Autofill will only add ONE per 'moved' row/column to cell-references, but this can be used to achieve the intended result...

Try this:
=OFFSET($C$2,(ROWS($C$2:$C2)-1)*10,0)

In this all references to cells start at $C$2 (first parameter).
It will stay as is, due to the $-s.

The result from ROWS(...) will increase by one for each extra line it gets copied to, as the 2 in :$C2 will increase.

The -1 will allow "0" as result from (ROWS(...)-1), and finally *10 will make the formula reference every tenth row.

---

To adapt this for using columns instead:
replace ROWS with COLUMNS and then make :$C2 be :C$2 instead.