In Calc, is there a way to auto-increment sheet references that appear in formulas?

tomcloyd · September 20, 2023, 6:29am

I have trying to solve this problem all evening, with no hint of success:

Let’s say I have 11 sheets in a Calc spreadsheet. Sheets 2 to 11 contain data summarized in cells A1:E1 of each sheet.

I want to capture these summary data in sheet 1, starting with cell A1:E1. But I then want to copy those cells into the next row and have the references point to the next SHEET - sheet 3.

The general problem is that I have a number of sheets from which I want to capture such summary data, but without having to manually edit the cell formulas each time to get them to point to the next sheet.

Incidentally, the sheet names are NOT sheet1, sheet2, etc. So moving to the next sheet needs to use some general method of incrementing the sheet reference.

Is there an artful way to do this?

Thanks for any ideas offered!

Wanderer · September 20, 2023, 6:42am

With INDIRECT you can convert a String to a cell-reference.
.
You need something incrementing your formula, so use the ROW-Number.
.
In A2 for example for Sheet2 (not tested)
=INDIRECT("Sheet"&ROW()&".A1")

Disadvantage: as part of the string the Cell also will not adapt, if dragged. So you have to do this yourself or via similiar formula and COLUMN()-reference.

Documentation/Calc Functions/INDIRECT - The Document Foundation Wiki.

tomcloyd · September 20, 2023, 6:45am

Wow…so impressive to get such a quick and interesting response. It looks promising. And I need only to increment the sheet reference. The cell references are invariant.

Thank you so much.

Villeroy · September 20, 2023, 6:55am

Calc supports relative sheet references.
$Sheet1.$A$1 refers to exactly that cell when you copy the reference.

On the next sheet, Sheet1.$A$1 (without $) refers to <previous_sheet>.A1.

Wanderer · September 20, 2023, 7:12am

Yes, but if I read the question right @tomcloyd wishes to collect some kind of summary on the first sheet, so he is only advancing rows, not sheets, but needs to reference next sheet in next row.

tomcloyd · September 21, 2023, 1:38am

Yes, the problem is that I’m wanting to collect data across a range of sheets. This is really something that looks like it needs a programmatic solution, but I don’t do Basic.

JohnSUN · September 20, 2023, 7:53am

Have you thought about doing this job using a macro? For example, using a macro recorder, record the sequence of actions “go to sheet - select A1:E1 - copy - go to cell A2 - paste special as links”, slightly modify this code and apply it to all sheets from the second sheet to the last sheet? I tried. It turned out that this is not very difficult:

Sub collectAllSheetsInFirst
Dim oSheets As Variant
Dim i As Long
	oSheets = ThisComponent.getSheets()
	For i = 1 To oSheets.getCount()-1
		copyRngAsLink(i+1, i)
	Next i
End Sub

Sub copyRngAsLink(sourceSheet As Integer, targetRow As Integer)
Const RANGE_TO_COPY = "$A$1:$E$1"
Dim document As Object 
Dim dispatcher As Object 
Dim args1(0) As new com.sun.star.beans.PropertyValue
Dim args2(5) As new com.sun.star.beans.PropertyValue
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
args1(0).Name = "Nr"	: args1(0).Value = sourceSheet
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())
args1(0).Name = "ToPoint"	: args1(0).Value = RANGE_TO_COPY
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
args1(0).Name = "Nr"	: args1(0).Value = 1
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())
args1(0).Name = "ToPoint"	: args1(0).Value = "$A$" & targetRow
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

args2(0).Name = "Flags"	: args2(0).Value = "SVDT"
args2(1).Name = "FormulaCommand"	: args2(1).Value = 0
args2(2).Name = "SkipEmptyCells"	: args2(2).Value = false
args2(3).Name = "Transpose"	: args2(3).Value = false
args2(4).Name = "AsLink"	: args2(4).Value = true
args2(5).Name = "MoveMode"	: args2(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args2())
End Sub

What I like about this solution is that the macro is not at all interested in the names of the sheets, only their numbers, and therefore it does not matter whether it is Sheet1, Sheet2, Sheet3, or January, February, March, or John, Mark, Finch - the macro will do the job.

tomcloyd · September 21, 2023, 1:30am

This is precisely the sort of solution I’d like, EXCEPT that while I use macros I’ve created all the time I have not had much luck making sense of their code. I have programmed in at least 8 languages, but not this flavor of Basic and somehow it seems unusually dense to me. I’d like to get past that obstacle. I’ll try to find time to study what you offer. Your intelligent variable naming is very helpful! You time investment is drafting this up is most generous. Thank you so much!

karolus · September 21, 2023, 3:26am

Fortunatly LO has Interfaces not only in Basic , also to some other Languages

def collect_data_from_sheets(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    overview, *others = doc.Sheets
    cursor = overview.createCursorByRange(overview["A1:E1"])
    out = []
    for sheet in others:
        out.append(sheet["A1:E1"].DataArray[0])
    cursor.collapseToSize(len(out[0]), len(out))
    cursor.DataArray = out

This is python, you may install apso.oxt from here to organize your python-stuff for Libreoffice

tomcloyd · September 21, 2023, 4:11am

I honestly had NO idea this was the case. Very nice! I’ll definitely be looking into this. Again, thanks so much for this. You generous assistance is much appreciated.