2

I have a libreoffice calc document with multiple sheets. In the first sheet, I have a column that I want to be repeated as the first column in all other sheets.

I know that I can reference cells from another sheet via sheetname.A1 etc, but as the column has a lot of entries and grows over time, I would prefer not having to reference each cell individually. I also tried using split windows / freeze cells, but that doesn't seem to work across sheets.

Is there a way to add a column from one sheet to all other sheets?

3
+50

Yes and No. Yes, you can add a column to all other sheets, but it has its limits. LibreOffice is built so that its columns grow as needed, so when you clone a column to other worksheets the number of rows cloned will be the max number possible in those other sheets.

For example, say column A has 200 rows of data, and 500 rows currently in memory (i.e. you have scrolled down 500 rows in that session). When you clone the column over, 500 will be the max number of linked cells. Basically, Sheet1.A = Sheet2.A, but as far as Sheet 2 is concerned, Sheet1.A is 500 cells long. The first 500 cells of each will be identical, changing dynamically whenever Sheet1.A is changed, but only for the first 500 cells. LibreOffice (and excel too I think) simply don't have the functionality to symbolically set Sheeet1.A = Sheet2.A for all A.

So if your data is growing continuously, then there is no way to do this for all occurrences, other than just just repeating the cloning process when max is reached. You can help yourself out by scrolling down in libreoffice before cloning (i.e if you have 200 rows of data, scrolling down 2000 rows will buy you more time.)

If having a max is acceptable, following these steps is the quickest way to clone to all sheets:

  1. Right click the column header that you want to copy. Select "copy".
  2. At the bottom of the screen, ctrl+click the sheets you want to copy to. If you want to copy to all sheets, right click any of them and click "select all" (if you do this, be sure to ctrl+click the sheet you are copying from to deselect it, otherwise you'll get bad data.)
  3. Now, in any of the selected tabs, right click the header of the destination column (Note that this will erase all current data in that column. Insert new column if this is not acceptable) and select "paste special".
  4. In the pop-up, check the boxes "Paste All" under Selection and "Link" under Options.
  5. Finally, hit "OK" to finish.

This is the best way that I have found, given that full symbolic linking is not supported. Hope this helps.

| improve this answer | |
  • Thanks! Two small problems: 1) For me, it doesn't work for empty cells. If I fill the cells with no enty, it works (then deleting all no entry results in a 0 entry in the linked cells). It's not ideal, but it's good enough for me 2) I also wanted to be able to sort by this cloned column across sheets. I just assumed that once the column is cloned, this will easily be possible, but it doesn't seem so. But that's on me for writing a question missing important info. I'll give it a couple of days and then accept your answer. – tim Feb 10 at 9:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.