I have a column of numbers in one sheet. I wish to reference these in another
sheet but they need to be transposed so they are all in a single row. How do
I do this without manually having to do each cell?

Copy, Paste Special, Transpose.

Katie wrote:

I have a column of numbers in one sheet. I wish to reference these in another
sheet but they need to be transposed so they are all in a single row. How do
I do this without manually having to do each cell?

1. Copy the col of items (the number of items should not exceed the max
number of cols in any sheet, viz: 256 in xl2003/lower)
2. Right-click on the startcell in the other sheet Paste special Transpose
That should do it for you. Any good? Hit the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Katie" wrote:
I have a column of numbers in one sheet. I wish to reference these in another
sheet but they need to be transposed so they are all in a single row. How do
I do this without manually having to do each cell?

thinking the OP wants to transpose her references, not a hard copy of the
cells.

=OFFSET(Sheet2!$A$1,0,ROW()-1)

Should do it.. copy and paste down to reference across Sheet2's columns down
your new sheet's rows.

"Max" wrote:

1. Copy the col of items (the number of items should not exceed the max
number of cols in any sheet, viz: 256 in xl2003/lower)
2. Right-click on the startcell in the other sheet Paste special Transpose
That should do it for you. Any good? Hit the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Katie" wrote:
I have a column of numbers in one sheet. I wish to reference these in another
sheet but they need to be transposed so they are all in a single row. How do
I do this without manually having to do each cell?

Assume your original data is in B5:B10 on Sheet1. On Sheet2 in cell
C4, for example, enter the following formula and copy it across the
columns for as many rows as are in the orignal data.

Change the reference to Sheet1!$B$5 to the first cell of the orignal
data. Change $C$4 to the first cell containing the formula below.

=OFFSET(Sheet1!$B$5,COLUMN()-COLUMN($C$4),0,1,1)

See also http://www.cpearson.com/excel/ColumnToTable.aspx and
http://www.cpearson.com/excel/TableToColumn.aspx

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Tue, 1 Sep 2009 07:15:01 -0700, Katie
wrote:

I have a column of numbers in one sheet. I wish to reference these in another
sheet but they need to be transposed so they are all in a single row. How do
I do this without manually having to do each cell?