Using a worksheet function to create an unusual cell reference pat

[Josh Craig]

Can I create a worksheet function to say 'equals this cell reference but six
rows over'?

I want something like this:
in b1:
=a1
in b2:
=cell referenced in b1 plus 6 rows (i.e. g1)
in b3:
=cell referenced in b2 plus another 6 rows over (i.e. m1)

I'm sick of manually selecting cells!

 

[Sheeloo]

Use this in B1
=INDIRECT("R1C"&((ROW()-1)*6+1),FALSE)

and copy down...

 

[Josh Craig]

Thanks Sheeloo. It was the 'indirect' part of the formula I couldn't work out.

 

[T. Valko]

Here's a non-volatile approach:

=INDEX($1:$1,(ROWS(B$1:B1)-1)*6+1)