help needed with transposing of data in excel

[lardshow]

Hi all

a collegue of mine has a relativly simple problem in excel. she has a table (just some data in rows and columns - not a real table) of data set up and needs to copy the sum created in this table to a different table but transpose it from 1 column 19 rows to 1 row 19 columns.

the easiest way to do this would have been to use =$Q67 then drag the cell across to fill the blanks, this unfortunatley doesnt work as the numeric part of the formula doesnt change as the cell doesn't go down it goes across. without keying each "=Q67", "=Q68" for each cell which is too time consuming (its a large spreadsheet) is there another way to pick up the data from Q12:Q31 and paste it into cells W6:W25 which allows the data values held in the table if changed to reflect in the newly pasted ones? the second part of this problem is the the next lot of data is held 55 rows further down, then the next a further 55 rows etc etc. I've been playing around this afternoon with VB code to try and get what i want but its not quite right. unfortunatley i cannot supply a copy of the spreadsheet as it is confidential but i can try to explain the layout if this post doesnt make any sense.

thanks in advance

brian

[random1970]

What you want to do isn't too hard if you learn to use the Transpose Formula. It's an array formula. To use your example, if you have a 1 x 19 table (1 row high, 19 columns wide) and you want to transpose it, select a 19 x 1 array (19 rows high, 1 column wide) of cells, go to the formula bar and type in =transpose( then select the array you wish to transpose. Close the bracket, then type CTRL SHIFT ENTER (all at once) to apply the array formula. It should do the trick. Try with a small array (e.g. 1 x 5) to see how this works. The size of the array doesn't matter.

If you run into trouble, click on the fx button next to the formula bar and go to the Excel help on this function. You can read examples, see screenshots and download sample files from there.