Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transposing a column (2003)

    =OFFSET($A$1,COLUMN()-COLUMN($B$1),0,1,1)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Transposing a column (2003)

    Andrew

    Have you tried

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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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