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.