Results 1 to 6 of 6

20050512, 22:10 #1
 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?

20050512, 22:18 #2
 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.

20050512, 22:24 #3
 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.

20050513, 23:14 #4
 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?

20050513, 23:43 #5
 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.

20050514, 01:35 #6
 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.