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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SteveC - 18 Oct 2006 01:28 GMT

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

Reply to this Message

Biff - 18 Oct 2006 02:30 GMT

>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

>I have this in cell L9.
>
[quoted text clipped - 19 lines]
>
> SteveC

Reply to this Message

SteveC - 18 Oct 2006 02:53 GMT

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

> >I have this in cell L9.
> > =INDEX(Sheet1!$F$36:$F$51,COLUMN()-11)
[quoted text clipped - 40 lines]
> >
> > SteveC

Reply to this Message

Biff - 18 Oct 2006 03:45 GMT

You're welcome. Thanks for the feedback!

Biff

> Great! Extremely useful formula. Thanks very much for the explanation
> too.
[quoted text clipped - 46 lines]
>> >
>> > SteveC

Reply to this Message