Autofill cells in Excel with a different series than 1 1

[MMSMSD]

This is hopefully easy for someone else but I don't deal with it often. I have a pointer in a cell to a cell in another sheet in the same workbook:

='ANOTHERSHEET'!A1006

I need for the next cell down to point 9 rows down from the last one:

='ANOTHERSHEET'!A1015

...and to continue until about row 6000. If I select the original cell and drag down, it will increment the pointer by 1. If I go into Autofill Options and set it to 9, it grabs the referenced cell and increments that number by 9, instead of incrementing the pointer itself. I've been manually typing in these cells but would MUCH rather find a way to auto-fill what I need and save a day of mindless typing. I have 40 columns to alter like this! Any help is appreciated. Thanks.

Michael

 

[combo]

You can combine OFFSET and ROW functions, referencing to A1006:
[tt]=OFFSET(ANOTHERSHEET!A$1006,(ROW(ANOTHERSHEET!A1006)-ROW(ANOTHERSHEET!A$1006))*9,0)[/tt]
and copy down.

combo

 

[mintjulep]

INDIRECT() with a helper column containing the row of interest might be simpler and more transparent.

 

[MMSMSD]

Combo's solution worked like a charm. Thanks!