Non consecutive Rows converted to columns on another worksheet?

[mjmdaly]

Hi,
I can't seem to find a thread that has this same request, so forgive me if this is redundant to one:
I have a worksheet with a series of data in rows. Every 40th row is a sum of the previous forty rows.
I have a second worksheet that in a series of columns uses the 40th row sums from the other sheet. So column 1 uses row 40, column 2 uses row 80 data, etc.
Every time I update this, I create the new column using a copy and insert copied cells and then have to go to each field on the new column that uses this other worksheet to correctly update the formula for the new row. As when I do it this way, if Column 1 cell a1 formula said "worksheet2!A40, then the new column 2 cell b1 says "worksheet2!B40, where I want to make it be worksheet2!A80. I have tried highlighting a series of these corrected columns and dragging the formula over but still get a change in columns vs row from the other sheet.

I am looking to see if there is a way to do something like (in layman's expression):
(first column) worksheet 1, cell a1 = worksheet 2 cell A40
(second column) worksheet 1, cell b1 = worksheet 2 Cell A80
(third column) worksheet 1, cell c1 = worksheet 2 Cell (A80+40)
etc
Does that make sense. It may be simple but I am apparently an excel simpleton.
Thanks

[shg]

In A1 and copy right,

=INDEX(worksheet2!$A:$A, 40*COLUMNS($A1:A1))

[mjmdaly]

Fantastic. Thank you shg.

[shg]

You're welcome.