Excel Columns And Rows

[Barquentine]

On one worksheet I have several columns, each one is a list of info about one file.
On a second worksheet I need that column to be copied as a row.
I used the 'copy, then 'paste special/transpose' function.
But doing one column at a time is tedious.
Is there a way to use '=' to copy a whole column as a row in the second worksheet, and to drag down that function so Excel will understand it need to copy the next column and so on from the forst w/s to the second w/s?

Not sure if I've explained this clearly. Any help much appreciated.

[bibblebub]

if you right-click the mouse to do the pasting then one of the paste options is to transpose the copied column into a row

[Old_Geezer]

Not a lot of help I'm afraid but it does seem as if you need to do the tedious copy/transpose for each. If there is a way to format cells beforehand I can't see it, for you would not be formatting individual cells, you would be asking for processing and data shifting before the paste, which is different.

Maybe someone can create a macro for you ?

[Hammer]

You can copy and transpose more than one column at a time. Highlight 10 columns, copy, then paste special transpose and all 10 columns will be pasted as rows.

Or, in sheet2, assuming your data starts in cell A1 on sheet1, enter the formula "=INDEX(Sheet1!$A:$Z,COLUMN(),ROW())" without the quotes, then you can drag the formula down and across as you suggested. Check out the INDEX formula in excel's help to see what it does. This assumes that your origianl data is in columns A-Z of sheet1 (hence the $A:$Z in the formula which you can change if necessary)

[Barquentine]

Thanks to all who replied. I discovered an Excel forum which explained I can transpose an array and use the OFFSET function to autofill that down the worksheet.

[Barquentine]

Hammer - I just tried your solution which works as well. Doh!I just wasted 18 days looking for the answer when it was provided by Hammer here already.