Search code examples
libreofficeautofilllibreoffice-calc

LibreOffice Calc - how to drag-fill a series from multiple rows that all reference the same row

I have cells in a few rows that all reference one row on another sheet:

=Sheet2.A1
=Sheet2.B1
=Sheet2.C1

When I select these cells and drag the fill handle to fill the cells below, Calc fills them like this:

=Sheet2.A4
=Sheet2.B4
=Sheet2.C4
=Sheet2.A7
=Sheet2.B7
=Sheet2.C7
etc.

I guess it's doing this because each new row is three cells below the original row.

This is not the behaviour I want - I want to fill them in a series, like this:

=Sheet2.A2
=Sheet2.B2
=Sheet2.C2
=Sheet2.A3
=Sheet2.B3
=Sheet2.C3
=Sheet2.A4
=Sheet2.B4
=Sheet2.C4
etc.

Any ideas how to do this?

I'd like to use either a formula or a manual way of doing this - I'm not familiar with macros so would rather avoid if possible, please.

Solution

  • Manual way (I like that suggestion, I never even thought about using Calc to automatize the process of filling in spreadsheet formulas before, but it's doable):

    1. Open a new Calc file
    2. Format cell A1 as text
    3. type =Sheet2. into A1 (A1 should now display =Sheet2. literally)
    4. type A B C into B1, B2 and B3 respectively
    5. select B1 to B3 and drag down (it should repeat series A,B,C,A,B,C...)
    6. type =FLOOR((ROW()-1)/3)+2 into C1 (ROW() starts counting from 1 hence the -1)
    7. drag that down (it should give you the series 2,2,2,3,3,3,4,4,4...)
    8. type =A$1&B1&C1 into D1 (D1 should now display =Sheet2.A2 literally)
    9. drag D1 down and it should give you your desired series
    10. NOW: copy that
    11. paste it into any notepad or similar plain text editor
    12. copy that
    13. paste it into the Calc spreadsheet you wanted to have this series of references in first place
    14. it should open a text import dialogue - just make sure everything is pasted into one column and hit OK

    Voilà

    • pros: when your colleagues see you do that you will be seen as LibreOffice god from now on
    • cons: not very dynamic (you'll have to repeat the whole process if something changes)

    Formula way: Use OFFSET. E.g: =OFFSET(Sheet2.$A$2;FLOOR((ROW()-1)/3);MOD(ROW()-1;3))

    • pros: easier to modify
    • cons: if the results are not what you're expecting, it's harder to debug