Chit Chat
Welcome to Live View – Take the tour to learn more
Start Tour
There is currently 1 person viewing this thread.
Here's the scenario; In a workbook I have a worksheet that has a vertical column with the results of Jan, Feb, March, which I update manually on a daily basis.
To calculate averages, on another worksheet I have Jan, Feb, March on a horizontal. Each month I copy the column of the second work sheet and paste it in the column next to it, but I can't seem to find how to capture the vertical column of the first sheet to the horizontal column in the second. Anyone here know the formula for this?
I'm not sure I've explained myself right but please fire questions if needed. TIA
To calculate averages, on another worksheet I have Jan, Feb, March on a horizontal. Each month I copy the column of the second work sheet and paste it in the column next to it, but I can't seem to find how to capture the vertical column of the first sheet to the horizontal column in the second. Anyone here know the formula for this?
I'm not sure I've explained myself right but please fire questions if needed. TIA
Show More
I think you need to use the TRANSPOSE funciton of i am reading your question correctly. It is an array formula I believe so you would need to press CTRL+SHIFT+ENTER instead of just ENTER or it wont work. I wouldnt do this myself as there are better ways to do it using INDEX & MATCH Functions combined imo.
Say you have the data on Sheet 1 in A1:A10. You want it in Sheet 2 A1:J1.
Select the range A1:J1 on sheet 2. A1 must be the active cell. In this cell type =TRANSPOSE(
The go back to Sheet 1 and select range A1:A10. Then press and hold CTRL and SHIFT. Keep these held then press ENTER. Then Sheet 2 A1:J10 will be populated with the data on Sheet 1.
You will notice that the formular will look like this:
{=TRANSPOSE(Sheet1!A1:A10)}
You cannot type the curly brackets. They go in there when you press CTRL+SHIFT+ENTER.
There are better ways of doing this though.
Select the range A1:J1 on sheet 2. A1 must be the active cell. In this cell type =TRANSPOSE(
The go back to Sheet 1 and select range A1:A10. Then press and hold CTRL and SHIFT. Keep these held then press ENTER. Then Sheet 2 A1:J10 will be populated with the data on Sheet 1.
You will notice that the formular will look like this:
{=TRANSPOSE(Sheet1!A1:A10)}
You cannot type the curly brackets. They go in there when you press CTRL+SHIFT+ENTER.
There are better ways of doing this though.
OK, done that and it worked exactly as you said however; it doesn't solve my dilemma. Lets say I want to add an a11 and then for k1 I just want to copy and paste j1 to get the a11 automatically.. how do I do that?
Thanks for the input, it is appreciated
Thanks for the input, it is appreciated
Try this:
=INDEX(Sheet1!$A:$A,COLUMN(A:A))
Put this formula in Cell A1 on Sheet 2. As you drag it accross it should transpose the vertical data on sheet 1 into horiszontal in sheet 2.
=INDEX(Sheet1!$A:$A,COLUMN(A:A))
Put this formula in Cell A1 on Sheet 2. As you drag it accross it should transpose the vertical data on sheet 1 into horiszontal in sheet 2.
Bingo! You're a star Barry, that's exactly what I wanted. Thanks a lot! :)
Hey Dude; About right mate! (spurs)
Hey Dude; About right mate! (spurs)
Post Your Reply
Please login to post a reply.
Wonder
Instance ID: 13539