Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paste Vertical Columns to Horizontal Rows (Excel 2003)

    I am having a terrible problem. Attached is an example. What I want to do is take existing vertical cells in one worksheet and paste their reference horizontally on another sheet. I have used the paste special to transpose values, but I cannot seem to transpose a reference or a formula from one sheet to another in the same manner. Does anyone know how to take the vertical list of values in sheet 1 and transpose the sheet 1cell reference to sheet 2 so that when the sheet 1 values change, so does sheet 2?

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

    Re: Paste Vertical Columns to Horizontal Rows (Excel 2003)

    Paste Special won't allow you to paste a link AND transpose at the same time. If you want to use formulas that can be dragged easily to other cells, you can use INDIRECT: in cell B3 on Sheet2, enter

    =INDIRECT("Sheet1!A"&COLUMN())

    and use the fill handle (the black square in the lower right corner of the cell) to fill right to G3. Similarly, in cell B7, enter

    =INDIRECT("Sheet1!C"&COLUMN())

    and fill right to G7. It would also be possible to create a macro to create simple link formulas in B3:G3 and B7:G7 on Sheet2.

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste Vertical Columns to Horizontal Rows (Excel 2003)

    Thanks! That was great. I must have searched for an hour for a solution and I never ran across the INDIRECT function. This is the first time I have heard of it. Works perfect.

  4. #4
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste Vertical Columns to Horizontal Rows (Excel 2003)

    Hans,

    I have worked with this formula a little bit now and wonder if you would have the patience to assist me a little more. Good information on the INDIRECT function seems scarce on the internet and even in my formulas book. As far as I can tell, the structure of the formula does not seem to work in reverse, ie, to "transpose" rows to columns. Can you tell me how I would take sheet 1, row 7's values of 6, 4, and 24 and likewise put them on sheet 2 in a column?

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

    Re: Paste Vertical Columns to Horizontal Rows (Excel 2003)

    The exact formula depends on where you put it. And since you now want to vary the column instead of the row, it has to be done slightly differently. Say that you want to begin in a cell in row 11 on Sheet2, for example in B11. Enter the formula

    <code>=INDIRECT("Sheet1!R7C"&ROW()-10,FALSE)</code>

    and fill down to B13. If you had put the formula in B17, you'd have used -16 instead of -10. The argument FALSE tells Excel that this is a R1C1 style reference, not an A1 style reference.

    You can also use

    <code>=OFFSET(Sheet1!$A$7,0,ROW()-11)</code>

    and fill down to B13. If you had put the formula in B17, you'd have used -17 instead of -11.

  6. #6
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste Vertical Columns to Horizontal Rows (Excel 2003)

    Ahhhh, now I get it. Row1 Column1 format. And then tell it where to start. Its tough to think through that logic. Ill work with it some and see if I cant improve my skills a little. Thanks again- that was a huge light bulb you turned on.

Posting Permissions

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