Ask Your Question

Merging data between sheets

asked 2017-11-09 22:40:45 +0100

kdawes01 gravatar image

It's probably blindingly obvious how to do this - But I'm batting zero!

Calc may not be the best way to play with text, but here's what I'm trying to do...

I have a workbook with numerous sheets. Each sheet (except for Sheet1) has 1 column consisting of an indeterminate (and changeable) number of rows of text.

On Sheet1 I want a mirror of the contents of each of the other sheets.

I could do a Paste Special from the other sheets to Sheet1, but if the contents the other sheets are changed or rows are added or subtracted sometime down the line, Sheet1 doesn't automatically reflect the changes.

So... What I want to do (if at all possible) is to enter a formula in, say, Sheet1 Column1 Row 3 and have it pull all rows with data from Sheet2 Column1

Just because I want eggs in my beer... If, when the data is pulled from Sheet2, up to 3 empty rows in the data could be allowed (in case there are blank rows in the middle of the data), that would be gravy!!

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2017-11-09 23:03:13 +0100

m.a.riosv gravatar image

Use a matrix reference in the first for other sheets.


Use [Ctrl+Shift+Enter] to introduce the formula without the brackets they are added automatically.

Entering Matrix Formulas

edit flag offensive delete link more

answered 2017-11-10 23:06:15 +0100

Jim K gravatar image

updated 2017-11-12 09:46:49 +0100

Array formulas can be tricky to use, so it may be better to simply use an ordinary formula and then fill down. Put this in cell A3 of sheet 1.


Then drag to fill the formula down as far as needed, or select cells and press Ctrl+d to fill down. Typing a cell address such as 10000 in the Name box and pressing Shift+Ctrl+Up makes it quick and easy to select the cells.

I tested this with 10k rows and performance didn't seem to be a problem.


With the new requirements, it's time to write a macro. To run, put the code below into the user directory as described at

import uno
from import (

def copy_all_used_cells():
    oDoc = XSCRIPTCONTEXT.getDocument()
    oSheet1 = oDoc.getSheets().getByIndex(0)
    MAX_COLS = 26  # up to column Z
    all_data = [[] for dummy in range(MAX_COLS)]
    for oSheet in oDoc.getSheets()[1:]:
        oRange = oSheet.getCellRangeByName("A1:Z10000")
        oSheetCellRanges = oRange.queryContentCells(CELLFLAGS)
        xEnum = oSheetCellRanges.getCells().createEnumeration()
        while xEnum.hasMoreElements():
            oCell = xEnum.nextElement()
            oAddr = oCell.getCellAddress()
    for col, col_data in enumerate(all_data):
        if col_data:
            col_letter = chr(ord('A') + col)
            rangeName = "%s1:%s%d" % (
                col_letter, col_letter, len(col_data))
            oRange = oSheet1.getCellRangeByName(rangeName)
            data_tuples = ((val,) for val in col_data)  # 1 tuple per row

g_exportedScripts = copy_all_used_cells,

This copies the data into sheet 1 for each column on all other sheets. You'll probably want to make enhancements, for example, "maybe an empty row or two between the info from Sheet2 and Sheet3."

Also decide when to run the script -- at the push of a button or key combination, or whenever a sheet is changed (can be slow), or based on an event such as when the document is saved.

edit flag offensive delete link more

answered 2017-11-12 01:28:31 +0100

kdawes01 gravatar image

Thank you for your replies! Yes, those will work if the rows in Column1 of Sheet1 are populated those formulas.

However, if Sheet2 has 10 rows, Sheet3 has 10 rows, etc... And so then Sheet1 has 10 rows allotted for the Sheet2 rows, 10 rows for the rows in Sheet3, etc. - With maybe an empty row or two between the info from Sheet2 and Sheet3...

Then if Sheet2 gets edited to, say, 15 rows or maybe 5... Then info shown on Sheet1 from Sheet2 will be missing in the first case and the formatting messed up in the second.

No free lunch, I'm afraid!

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-11-09 22:40:45 +0100

Seen: 645 times

Last updated: Nov 12 '17