Calc: problem with indirect function

[manfred.lotz]

Tried to post yesterday evening but I was told that I had already posted to much as a new user, and have to wait for 20h till my next post.

Here is my minimal example

#! /usr/bin/env python3

"""
minimal example

Sheet 'defaults' contains cell A1 with content 'C'

Sheet 'main' contains
- C2 with content '42' (the answer to everything)
- F2 with the INDIRECT

"""
from xlsxwriter.workbook import Workbook


def main():
    workbook = Workbook('minimal_example.xlsx')

    sheet0 = workbook.add_worksheet('defaults')
    sheet0.write_string(0, 0, 'C')

    sheet = workbook.add_worksheet('Main')
    sheet.write(1, 2, '42')
    sheet.write_formula(1, 5, f'=INDIRECT(defaults.A1 & "2"')

    sheet.activate()

    workbook.close()


if __name__ == '__main__':
    main()

minimal_example.xlsx (5.6 KB)

[JohnSUN]

@manfred.lotz

f'=INDIRECT(defaults.A1 & "2"')

It seems to me that the closing parenthesis is missing here
f'=INDIRECT(defaults.A1 & "2")')

[manfred.lotz]

Oops, I had not seen this. But, in my real code I have the closing parenthesis.

Nevertheless, even if I add the ) the same error happens as before.

[JohnSUN]

I didn’t say that I found the cause of the error
By the way, about this remark:

@manfred.lotz

that the same named functions in excel and libreoffice work the same. But it seems there are subtle differences

I remembered one difference. The separator between the sheet name and the cell address in Calc is a dot, and in Excel it is an exclamation mark.

[manfred.lotz]

You are right. Now it works fine.

Before I had read in the Libreoffice documentation about INDIRECT

In case of R1C1 notation, ADDRESS returns address strings using the exclamation mark ‘!’ as the sheet name separator, and INDIRECT expects the exclamation mark as sheet name separator. Both functions still use the dot ‘.’ sheet name separator with A1 notation.

But this is not Excel…

Thanks a lot.

[mariosv]

Please take a look to help in Detailed Calculation, maybe can help.

Reference syntax for string reference
Formula syntax to use when parsing references given in string parameters.
This affects built-in functions such as INDIRECT that takes a reference as a string value.
Use formula syntax
Calc A1
Excel A1
Excel R1C1
Calc A1 | Excel A1

[erAck]

And if (like here) loaded from .xlsx then Excel A1 with ! exclamation mark sheet name separator is assumed, unless the file was written by LibreOffice and has an extended property that declares the syntax to be used (for which a non-! syntax of course Excel can’t handle).

[manfred.lotz]

Thanks for the link.