Using Index to Transpose, Update Reference Cells as you Drag Down
 

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

Using Index to Transpose, Update Reference Cells as you Drag Down
 

I have this in cell L9.
=INDEX(Sheet1!$F$36:$F$51,COLUMN()-11)
And separately, I don't really understand why the -11 works.

The reason -11 works is because you have the formula entered in cell L9 and
column L is column number 12. So:

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

As you copy across the COLUMN() increments by 1 and the result of the
subtraction also increments by 1.

So, if you want to transpose the entire range, I'll assume it's F36:H51:

=INDEX(Sheet1!$F$36:$H$51,COLUMNS($A:A),ROWS($1:1) )

Biff

"SteveC" wrote in message
...
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

Using Index to Transpose, Update Reference Cells as you Drag D
 

Great! Extremely useful formula. Thanks very much for the explanation too.

"Biff" wrote:

I have this in cell L9.
=INDEX(Sheet1!$F$36:$F$51,COLUMN()-11)
And separately, I don't really understand why the -11 works.

The reason -11 works is because you have the formula entered in cell L9 and
column L is column number 12. So:

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

As you copy across the COLUMN() increments by 1 and the result of the
subtraction also increments by 1.

So, if you want to transpose the entire range, I'll assume it's F36:H51:

=INDEX(Sheet1!$F$36:$H$51,COLUMNS($A:A),ROWS($1:1) )

Biff

"SteveC" wrote in message
...
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

Using Index to Transpose, Update Reference Cells as you Drag D
 

You're welcome. Thanks for the feedback!

Biff

"SteveC" wrote in message
...
Great! Extremely useful formula. Thanks very much for the explanation
too.

"Biff" wrote:

I have this in cell L9.
=INDEX(Sheet1!$F$36:$F$51,COLUMN()-11)
And separately, I don't really understand why the -11 works.

The reason -11 works is because you have the formula entered in cell L9
and
column L is column number 12. So:

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

As you copy across the COLUMN() increments by 1 and the result of the
subtraction also increments by 1.

So, if you want to transpose the entire range, I'll assume it's F36:H51:

=INDEX(Sheet1!$F$36:$H$51,COLUMNS($A:A),ROWS($1:1) )

Biff

"SteveC" wrote in message
...
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