how do I transpose items from another sheet?

[Katie]

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?

 

[Bob I]

Copy, Paste Special, Transpose.

 

[Max]

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

 

[Sean Timmons]

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.

 

[Chip Pearson]

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)

 

[sa11ys]

For multiple entry or use (this can be combined into a formula) ...Go to the cell with the data and click on it. Go to the top tab 'formulas' and then find 'define name' name the cell.
Go to the new sheet and click in the required box start typing = then the defined name and double click on it when it appears. then press enter. You can also use the defined name within a formula.
You can also enter = then go to the sheet and the cell and click in it and press enter (it will automatically enter the same number).
I use it for price lists where we have exchange rates. It means i can change the exchange rate on sheet 1 and all the linked formula automatically changes. It saves a huge amount of time. But I also use it on special price quotes where all the working is on one sheet and the final figures on another sheet. It saves time.
Either works until you break the connection.

 

[stevegelling]

I've needed to do the same thing, and with respect, those OFFSET formulas above didn't work, probably because I was doing something wrong but them being so complicated didn't help!

I've found that a simple TRANSPOSE formula just modified to reference the original sheet worked for me.

1. Lets say you have data in cells A1 through to A10 in sheet1 that you want to show in sheet2 in cells A1 through to J1 (10 rows of data to become 10 columns of data)

2. Highlight A1 through J1 in sheet2

3. In the text field type =TRANSPOSE(sheet1!A1:A10)

4. Press Ctrl + Shift + Enter

Done

I give credit to techonthenet.com. Excellent site