Ask Your Question

calc - copy row to column [closed]

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

DiesNuts gravatar image

updated 2020-08-11 14:29:41 +0100

Alex Kemp 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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-11 12:46:54.954098

2 Answers

Sort by » oldest newest most voted

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

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 +0100 )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 +0100 )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 +0100 )edit

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

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 +0100 )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 +0100 )edit

Question Tools

1 follower


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

Seen: 914 times

Last updated: Nov 07 '16