Ask Your Question

calc - copy row to column

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

DiesNuts gravatar image

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-11-07 15:39:31 +0200

karolus gravatar image


edit flag offensive delete link more


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.

AnthonyJH gravatar imageAnthonyJH ( 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.)

DiesNuts gravatar imageDiesNuts ( 2016-11-07 16:13:50 +0200 )edit

You need not drag down, you need only enter the Formula by <ctrl><shift><enter>

karolus gravatar imagekarolus ( 2016-11-07 16:19:12 +0200 )edit

answered 2016-11-07 15:43:21 +0200

AnthonyJH gravatar image

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.

edit flag offensive delete link more


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

karolus gravatar imagekarolus ( 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.

AnthonyJH gravatar imageAnthonyJH ( 2016-11-11 13:42:46 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 760 times

Last updated: Nov 07 '16