#1
|
|||
|
|||
Make row in one sheet equal column of another
I'm calculating an "complaint index" in one sheet where it ends up in a row and I want the values from that row to be copied to another sheet where they are meant to fill a column. I can get single values via this: ='complaint index'!G9 but when I try to "drag it out" so that Excel realises that after G9 H9 I9 comes J9 it doesn't get it. I'm not keen on entering the letter for each field individually. I hope you know what I mean and sorry for the basic question but it's driving me nuts and google didn't help me out... Thanks! |
#2
|
||||
|
||||
Hi,
The simplest solution would be to highlight your 'complaint index' range, select copy, then move to your other sheet, select the first cell of your destination column, and use "Paste Special..." with "Transpose". If you need a formula, please identify the size of the source range and the first cell of your destination column. Cheers, |
#3
|
|||
|
|||
Thanks for your reply.
The problem with the copy paste solution is that I don't have all the values yet (they will get updated every day so the formula solution would be best). Quote:
Cheers |
#4
|
||||
|
||||
Insert this formula in cell E9 of your destination sheet:
Code:
=INDEX('complaint index'!$G$9:$P$9,1,ROW()-8) Cheers, |
#5
|
|||
|
|||
It seems like an elegant solution but I can't get it to work.
I get an "Invalied cell reference error". Is there any chance you can explain what each part of the formula means? I can get this to work for a single field but autofill doesn't work... Quote:
|
#6
|
||||
|
||||
This INDEX function will return any value from a range where the row and column within the range is identified.
The first portion: Code:
'complaint index'!$G$9:$P$9 The second portion: Code:
1 The third/final portion: Code:
ROW()-8 Hope this helps, Cheers, |
#7
|
|||
|
|||
Thanks!
I registered just so I could say that this formulaic discovery was AMAZING! Thanks for sharing it!
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
If A1 greater than or equal to 1 Then A3 = A2*A1 | brians | Excel | 5 | 03-09-2010 12:04 PM |
copy cell from sheet 2 to sheet 3 macro | slipperyjim | Excel Programming | 1 | 02-18-2010 01:31 AM |
Template for Noting Sheet. | Goldenbird | Word | 8 | 05-25-2009 05:48 AM |
sheet linking, sorting | nickbfe | Excel | 0 | 05-19-2006 11:00 AM |
Enter Number on any sheet one time only. | paulrm906 | Excel | 1 | 04-28-2006 07:35 AM |