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$2Hallo
=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.