# calc - copy row to column

Say I have some data in sheet Sheet_A on R2:AA2

I now want to reference that data in Sheet_B in B2:B10

How do I do that?

If I write

 Sheet_B.B2 = Sheet_A.R$2  And then drag down, it will result in  Sheet_B.B3 = Sheet_A.R$2


But I want

 Sheet_B.B3 = Sheet_A.S$2  edit retag close merge delete ## 2 Answers Sort by » oldest newest most voted Hallo =TRANSPOSE(Sheet_A.R2:AA2)  more ## Comments Yes, this is a good solution, particularly if the range in Sheet_A is not expected expand in the future. Don't forget to use <ctrl><shift><enter> when entering an Array formula. ( 2016-11-07 15:49:33 +0200 )edit Hm ... can't seem to get this to work. For one, it DOESN'T actually transpose things. (If I drag, it get's displayed in Sheet_B in exactly the way it is displayed in Sheet_A. (Also, if I try to operate with this [e.g. take an average of multiple sheets, instead of simply copying from one], the results are bogus, but that wasn't part of the question.) ( 2016-11-07 16:13:50 +0200 )edit You need not drag down, you need only enter the Formula by <ctrl><shift><enter> ( 2016-11-07 16:19:12 +0200 )edit It's not the clearest of solutions but you could use the OFFSET and ROWS functions. By using the formula =OFFSET(Sheet_A.$R$2,0,ROWS($B\$2:B2)-1) in Sheet 2 B2, you should be able to drag-copy downwards. Of course, you'll not get the clear direct references to the cells in Sheet_A that you would like but the horizontal range of values will be transposed to the vertical.

See OFFSET function and ROWS function for further details.

Hope this helps.

more

Useless use of ROWS(…)
aslong we know the (static) source, we need no OFFSET's-Magics

( 2016-11-07 16:06:24 +0200 )edit

Agreed, if the source is static then don't use this solution. But if the source range is expected to increase and drag functionality in the target sheet is desired, then consider this solution.

( 2016-11-11 13:42:46 +0200 )edit

## Stats

Asked: 2016-11-07 11:16:23 +0200

Seen: 503 times

Last updated: Nov 07 '16