I have this in cell L9.

=INDEX(Sheet1!$F$36:$F$51,COLUMN()-11)

It works great when I drag across, transposing column data on another sheet
into a row.

But how do I get it to work so when I drag the formula down to cell L10,
that it changes to this:

=INDEX(Sheet1!$G$36:$G$51,COLUMN()-11)


Thanks.

And separately, I don't really understand why the -11 works. I viewed
Excel help and didn't understand. I found the -11 works for me by trial and
error. Any more intuitive explanation of choosing the right number like -11
would be great.

SteveC