Chit Chat
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
sort by:
Show
per page
Replies: 7
|
By:
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.
|
|
By:
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. |
|
By:
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 |
|
By:
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. |
|
By:
nice to see Aspro back
http://bit.ly/1xELkX7 |
|
By:
Bingo! You're a star Barry, that's exactly what I wanted. Thanks a lot! :)
Hey Dude; About right mate! (spurs) |
|
By:
scrap your second sheet, no point copy and pasting any data you already have.
On the second sheet, insert pivot table and use that to calculate averages, days p/l, months p/l or whatever you want it to show. |