# Thread: Transposing a column (2003)

1. ## Transposing a column (2003)

I have a column of figures in column A. I can use =TRANSPOSE(A1:A5) to pull information into cells B1:F1, but I want to revise this formula so that I can drag it down using the Fill Handle such that it will pick up the values from A6:A10, etc.. Any idea how this can be done? Thanks, Andy.

2. ## Re: Transposing a column (2003)

=OFFSET(\$A\$1,COLUMN()-COLUMN(\$B\$1),0,1,1)

3. ## Re: Transposing a column (2003)

That's ok when copying the formula into cells B1 to F1, but doesn't work if I drag it down the columns. That is, it doesn't then pick up information starting from cell A6 etc.

4. ## Re: Transposing a column (2003)

Andrew

Have you tried

=OFFSET(\$A1,COLUMN()-COLUMN(\$B1),0,1,1)

5. ## Re: Transposing a column (2003)

Try this in B1:

<code>=OFFSET(\$A\$1,ROW()*5+COLUMN()-7,0)</code>

Fill right to F1.
With B1:F1 still selected. fill down as far as needed.

6. ## Re: Transposing a column (2003)

Try:
=OFFSET(\$A\$1,COLUMN()-COLUMN(\$B\$1)+5*(ROW()-ROW(\$B\$1)),0,1,1)

In B1:F1 will be A1-A5
In B2:F2 will be A6-A10
Etc

Steve

7. ## Re: Transposing a column (2003)

Sorry, I didn't spot the dragging down. Hans and Steve did though <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•