References/Copy down or across

[Scott]

Say I've got some data in column A, and I want to
reference it, but not every cell, every 7th for example
in column B. So B1 depends on A1, B2 on A8, B3 on A15
etc. Is there an easy way to 'copy down' and make this
happen? Same question for row 1 instead of column A and
A2 instead of B2 and copying across.
Thanks!
Scott

 

[JE McGimpsey]

One way:

B1: =INDEX(A:A,(ROW()-1)*7+1)

Copy down as far as necessary:

Across:

A2: =INDEX(1:1,(COLUMN()-1)*7+1)

 

[Scott]

A thing of beauty. Thanks!
Scott

-----Original Message-----
One way:

B1: =INDEX(A:A,(ROW()-1)*7+1)

Copy down as far as necessary:

Across:

A2: =INDEX(1:1,(COLUMN()-1)*7+1)


.

 

[Gord Dibben]

Scott

Assuming data in row 1..........

=OFFSET($A$1,7*COLUMN(),0) drag across if entering in A2

Data in Column B..........

=OFFSET($A$1,7*ROW(),0) drag down if entering in B1

To enter the formula in another row or column......

=OFFSET($A$1,COLUMN()*7-7*C,) where C is the column you enter in

=OFFSET($A$1,ROW()*7-7*R,) where R is the row you enter in

Drag/copy these across or down.

Gord Dibben Excel MVP

 

[Scott]

Much appreiciated.
Scott

-----Original Message-----
Scott

Assuming data in row 1..........

=OFFSET($A$1,7*COLUMN(),0) drag across if entering in A2

Data in Column B..........

=OFFSET($A$1,7*ROW(),0) drag down if entering in B1

To enter the formula in another row or column......

=OFFSET($A$1,COLUMN()*7-7*C,) where C is the column you enter in

=OFFSET($A$1,ROW()*7-7*R,) where R is the row you enter in

Drag/copy these across or down.

Gord Dibben Excel MVP




.