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

[php-dorange]

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).

[sokol92]

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

[Villeroy]

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.

[sokol92]

@Villeroy

INDIRECT with ! as sheet separator works with any formula syntax

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

[Villeroy]

Hu?
ask124133.ods (12.7 KB)

[sokol92]

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.

[Villeroy]

The [Details…] button on the formula tab of the options dialog:

It’s complicated.

[sokol92]

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.

[php-dorange]

Thanks (Sokol92 and Villeroy) for help on this issue.

I do not understand every aspect of your discussion but it seems it was (very) complicated.
You point me to a more precise help file (INDIRECT with second parameter 0), in this help doc it was “clear” that Libreoffice do not handle the Excel syntax in indirect strings.
So there is no clear issue and perhaps no solution to my problem.

To make i more clear i attach a full example of what i want to do :
User select a dataset in the menu (listed here some sheets)
Then choosing a value in the dataset (for example a month)
Then it should display the value for the choosen month in the corresponding dataset

indirect-sample-124133.ods (22.9 KB)

I add some intermedaire formula to help debugging

Extract from help from Libreoffice (indirect) :
https://help.libreoffice.org/latest/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3153181

If you open an Excel spreadsheet that uses indirect addresses calculated from string functions, the sheet addresses will not be translated automatically. For example, the Excel address in INDIRECT("[filename]sheetname!"&B1) is not converted into the Calc address in INDIRECT(“filename#sheetname.”&B1).

[sokol92]

@php-dorange, thanks for the informative example file.
I don’t see any problems with this topic.

1. We want the file to be usable by MS Excel fans after exporting it in .xlsx (.xlsm, …) format.
   Excel “understands” only the exclamation mark as a sheet name separator, so we can only use this separator.
2. Open your file and confidently enter “!” in cell B10 (there is no other option, as follows from point 1.). We see the value #REF!, but we don’t get upset and move on.
3. During the discussion with my colleague @Villeroy , we found out that the file has a property that is responsible for the interpretation of the file separator by the INDIRECT function.
   In your file, this property indicates that both “.” and “!” sheet name separators are allowed. What’s the matter?
4. Once again, we look closely at your formulas through the eyes of an Excel user and see a “strange” # sign before the sheet name, which is not used in Excel (in Calc, this is a sign of absolute addressing of the sheet name). We change the formula in cell B12 to:

=B5&B10&B11

and look at the result with satisfaction.

[php-dorange]

Ok Thanks.
Sorry but i’ not sure to understand everything, perhaps also because my english was not fluant (i’m french speaking).
But when you said in point 2 : “we don’t get upset and move on” : OK but i cannot let this append in my spreadsheet before sending it to my customer. I must verify before…

And i dont under stand the point 3 you said that in my file the porperty handle both “.” and “!”, it was not the case, when i onpen the smaple file and choose “!” for the syntax of the string for INDIRECT : it break all calculation with “#REF” and nothing works anymore.

I thnik we don’t understand each other.

What i need is a spreadsheet that works native with LibreOffice (for me the creator) and that i can export to XLS to communicate and doing this without change things each time.

[sokol92]

I have attached your file with the correction I made.
I saved this file in .xlsx format and opened it in MS Excel 2016:

indirect-sample-124133.ods (12.3 KB)

[sokol92]

In Calc (.ods and .xlsx files look the same after opening in Calc)::

[karolus]

why not simply »better data-layout« instead?
avoid_indirect_with_better_data_layout.ods (12.2 KB)

[sokol92]

@karolus

why not simply »better data-layout« instead?

It is also useful to know how to work with INDIRECT.

[karolus]

It maybe usefull to know… but in practice we should avoid »indirect« (IMHO)

[sokol92]

@karolus

we should avoid »indirect« (IMHO)

Volatile functions, particularly INDIRECT, should always be avoided (if possible).

[Zizi64]

@php-dorange

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

You can get the separator character by standard functions in a cell named “Sep” (I hope the Excel has functions ADDRESS, RIGHT and LEFT too):

=LEFT(RIGHT(ADDRESS(1;1;1;1;"Sheet1");5);1)

Then you can use the cell name in the formula of the INDIRECT function:

=INDIRECT(A3&Sep&"A1")

[php-dorange]

The file works perfectly, thanks.
But, i’m sorry, i don’t see or understand what you’ve done.
I compare formula it was the same…

[sokol92]

Cell B12.
Old formula:

="$"&B5&B10&B11

New formula:

=B5&B10&B11