Search code examples
libreoffice-calc

How can I autocomplete formula that can change column name when dragged down?

I want to set formula on a row basis that takes value from columns in LibreOffice. For example, I have values as shown below in Sheet 1:

    A   B   C   D   E   F   G   H   I
1   A1  B1  C1  D1  E1  F1  G1  H1  I1
2   A2  B2  C2  D2  E2  F2  G2  H2  I2
3   A3  B3  C3  D3  E3  F3  G3  H3  I3

In Sheet2, I need the values of each column in Sheet1 as row entries (as =Sheet1.A1) as shown below:

=Sheet1.A1
=Sheet1.B1
=Sheet1.C1
=Sheet1.D1
=Sheet1.E1
=Sheet1.F1
=Sheet1.G1
=Sheet1.H1
=Sheet1.I1

I should be able to complete the cells when I drag down the small square to get this pattern.

How can this be done in LibreOffice?

Solution

  • Just use the TRANSPOSE() function as array function:

    1. Put the cursor into cell Sheet2.A1;

    2. As formula, enter

      =TRANSPOSE(Sheet1.A1:I1)

    3. Hit CTRL+SHIFT+Enter, to enter the formula as array function.

    Result:

    enter image description here

    To edit the formula afterwards, you'll have to select the complete resulting array (Sheet2.A1:A9) first. Otherwise, LO Calc will complain about changing only a part of an array.