# 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``

Hallo

``=TRANSPOSE(Sheet_A.R2:AA2)``

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 when entering an Array formula.

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.)

You need not drag down, you need only enter the Formula by

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.

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

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.