How to reference another sheet transposed

sho1sho1sho1 · September 15, 2023, 9:24pm

I have a general question about referencing another sheet transposed.
I know how to reference Sheet1 using =$Sheet1.A1, but I need to transpose on Sheet2… dragging the box will give me the same as Sheet1.
I think I need something that will do this: $Sheet1.A1 … B1 … C1 … D1 (dragging down on Sheet2)
$Sheet2.A2 … B2 … C2 … D2 (dragging down on Sheet2)

Sheet1
one two three four
1 2 3 4

Sheet2
one 1
two 2
three 3
four 4

I hope you all understand what I am trying to accomplish!
Thanks for helping out!

Zizi64 · September 16, 2023, 4:17am

Try this formula in the Cell A1 of the Sheet2, and then drag is right and down:

=INDIRECT("Sheet1." & ADDRESS(COLUMN();ROW();))

Villeroy · September 16, 2023, 11:51am

Array function TRANSPOSE
Copy&Paste-special with option “Transpose”

karolus · September 16, 2023, 9:39am

With a »finite« Range you may simply use something like:

=TRANSPOSE($Sheet1.A1:ZZ999)

Zizi64 · September 16, 2023, 10:21am

Just a note:
You must use “Array function” on the target range.

karolus · September 16, 2023, 12:15pm

with me and:

Version: 7.4.7.2 / LibreOffice Community
Build ID: 40(Build:2)
CPU threads: 4; OS: Linux 6.1; UI render: default; VCL: gtk3
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Debian package version: 4:7.4.7-1
Calc: threaded

the »array« -evaluation is triggered implicitly by the »TRANSPOSE«-function.