Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jun 2007
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    transposition (2003)

    the problem is to create references in sheet "trans" to cells in sheet "vert" without having to do this manually ...... can a formula be devised that will increment the row number as the copy/paste function is used to copy across columns .......? In other words, I want to be able to copy the cell in trans:b3 and paste into the cells trans:c3 to trans:m3 and end up with a horizontal set in trans that references the vertically oriented cells in vert from vert:b5 to vert:b16 .......
    Attached Files Attached Files

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

    Re: transposition (2003)

    Select B3:M3 on the Trans sheet.
    Type =TRANSPOSE(
    Activate the Vert sheet and point to B5:B16.
    Type )
    You should now see the formula =TRANSPOSE(Vert!B5:B16)
    Press Ctrl+Shift+Enter to confirm it as an array formula.

    Alternatively, enter the following formula in B3 on the Trans sheet:

    =OFFSET(Vert!$B$5,COLUMN(B3)-COLUMN($B$3),0)

    Drag to the right to M3, or copy/paste.

  3. #3
    Lounger
    Join Date
    Jun 2007
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: transposition (2003)

    As usual Hans, you are my hero ....... I am beginning to believe that there is little that I actually know about Excel .......... thank you

Posting Permissions

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