How to make a custom autofill continue instead of repeating?

[Blumenkind]

I have 62 sheets that have names that i need to enter into different calculations. For example: =MIN($funnyname.D:$funnyname.D) and then follow it with the next name: =MIN($hilariousname’.D:$hilariousname.D) and so on another 60 times. My idea was to make a list over “tools → options → Libre office calc → Sort list” and let it autocomplete. Unfortunately inside the function it is only repeating the names i have already written. But it won’t continue my list. But outside if i just enter the names into empty fields it is autocompleting my list just like i want it to. Can anyone give me a hint on how to solve my predicament?

[karolus]

Hallo
It was and will be never a funny|hilarious Idea to distribute similar data over drölfzigs of sheets.
the following python-code generates a List of Sheetnames except the first Sheet.

def list_sheetnames_below_current_Selection(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.CurrentSelection
    sheets = doc.Sheets
    sheet = sel.Spreadsheet
    sheetnames = sheets.ElementNames[1:] #start with second
    out = tuple(zip(sheetnames)) 
    cursor = sheet.createCursorByRange(sel)
    cursor.collapseToSize( len(out[0]), len(out))
    cursor.DataArray = out

it may be extend to produce the full Formulas eg.

def list_sheetnames_below_current_Selection(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.CurrentSelection
    sheets = doc.Sheets
    sheet = sel.Spreadsheet
    sheetnames = sheets.ElementNames[1:] #start with second
    out = [[ f"=MIN({name}.D:D" ] for name in sheetnames ]
    cursor = sheet.createCursorByRange(sel)
    cursor.collapseToSize( len(out[0]), len(out))
    cursor.FormulaArray = out

[Blumenkind]

Thank you for the solution. Unfortunately i am too dumb to run a pythonscript in Libre office calc and ran into error messages. But as i understand your solution i would need to do it for all operations id like to do Therefore once for SUM/Average/Quartile and so on. I have now rethought my approach and went for a bit more clicking. But probably it will be less time intensive then learning how to run python in LO.
I thought im overlooking a simple solution but i guess i wasnt, nevertheless thank you for your idea it helped me find my approach :S

For all the googlers that found this here, this how i solved it for myself - solution above might also be what you are searching for:
Since all tables have the data in the same columns I did a template for my calculations and pasted it into all sheets making sure calculated informations would always be in the column to the right. Therefore d2,e2,f2,g2 and so on. Then i needed to once enter all the 62 sheet references “$funnyname.D2” and could drag them to the right going e2,f2 and so on copying the info i wanted to originally calculate on the first sheet. Clicking result is the 62times sheet names, but at least not hundreds to thousands of times. And since i already had a list of my sheet names i posted them into one column and next to it my repetition of the same sheet reference =$‘funnyname’.d2. Then i went berserk mode on: stgr+c → f2 → stgr+v.

[LeroyG]

You can do a try to INDIRECT function.

[joshua4]

Here is what @LeroyG is likely thinking.

There is still a macro involved (overkill macro, sorry…) but it is just a BASIC macro that can be pasted in using Tools>Macros>Edit Macros. Google using the BASIC macro organizer for LibreOffice if you need info on creating a Module for pasting a macro into your project from this example. Or post back here, I suppose.

BulkSheetNames.ods (19.9 KB)

[Blumenkind]

I marked it as solution so it won’t be shown as an open question anymore. In future i might come back to it, but most likely i will use databases from now on. :S