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!

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

and copy down...


"Josh Craig" wrote:

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!

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

"Sheeloo" wrote:

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

and copy down...


"Josh Craig" wrote:

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!

Here's a non-volatile approach:

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

--
Biff
Microsoft Excel MVP


"Josh Craig" wrote in message
...
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!