Accessing a sheet via it's name with LibreOffice and Excel

A have a bit complicate spreadsheet with several sheets and formula that build some cells address from String and INDIRECT, one part of it was the name of the sheet. It works fine.
But when i export in XLS, it doesnt works with Excel. The error came from the syntax to address a cells with sheet spec.

LibreOffice need someting like : “sheet1.A1” (to adress cell A1 in sheet “Sheet1”
Excel need something like “sheet!A1” ("!" instead of “.”)

When it was a simple addressing (like exemple above) it work LibreOffice translate the syntax.
But if the addressing is build from a formula and string concat, of cours it doesn’t work.
I think of a workaround, testing the software (LibreOffice ou Excel) but i don’t find anything.

The exact problem (example) :
=INDIRECT(A3&"."&"A1")
Where A3 contain the name of the destination sheet; ie “Sheet1” and A1 in “Sheet1” contain the data i need.

I need this because in one cell a can specify the sheet to use. Each sheet contain différent data but always formated in the same position on each sheet (for example 1 year per sheet).

You can use in Calc the exclamation mark as a separator in the INDIRECT function and thus solve the problem with exporting to Excel format.
To do this, open your file and change the option:
Menu / Tools / Options / LibreOffice Calc / Formula / Details;
Reference syntax for string reference: CalcA1 | ExcelA1 OK OK

1 Like

INDIRECT with ! as sheet separator works with any formula syntax. For R1C1 references like INDIRECT("Sheet1!R1C4";0), you have to pass a second argument False or 0.

And while we are in it: the semicolon as argument separator works with all locales, unlike Ecxel where the semicolon only works with locales using the comma as decimal separator.

I can’t agree with that.
See my previous answer and the attached file.
Indirect.ods (7.6 KB)

Hu?
ask124133.ods (12.7 KB)

Your file in settings.xml has a tag:

<config:config-item config:name="SyntaxStringRef" config:type="short">7</config:config-item>

My file has tag:

<config:config-item config:name="SyntaxStringRef" config:type="short">0</config:config-item>

See also here.

The [Details…] button on the formula tab of the options dialog:
Bildschirmfoto von 2025-07-07 18-39-55
It’s complicated.

I added a link to the previous post. The main thing is that the global parameter and the tag in the file use different enumeration.