Merging data between sheets

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!!

Use a matrix reference in the first for other sheets.

{=Sheet2.A1:A500}

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

Entering Matrix Formulas

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.

=IF(Sheet2.A1="","",Sheet2.A1)

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.

EDIT:

With the new requirements, it’s time to write a macro. To run, put the code below into the user directory as described at Python as a macro language - Apache OpenOffice Wiki.

import uno
from com.sun.star.sheet.CellFlags import (
    VALUE as NUM_VAL, DATETIME, STRING, FORMULA)

def copy_all_used_cells():
    oDoc = XSCRIPTCONTEXT.getDocument()
    oSheet1 = oDoc.getSheets().getByIndex(0)
    CELLFLAGS = STRING | NUM_VAL | DATETIME | FORMULA  # any value
    oSheet1.clearContents(CELLFLAGS)
    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()
            all_data[oAddr.Column].append(oCell.getString())
    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
            oRange.setDataArray(data_tuples)

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.

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!