References/Copy down or across

  • Thread starter Thread starter Scott
  • Start date Start date
S

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
 
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)
 
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)


.
Click to expand...
 
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
 
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




.
Click to expand...
 
Back
Top