Calc: problem with indirect function

English
#7

It is unlikely that the reason for the error is hidden here. Nevertheless, let’s clarify one more point - are the interface languages for Calc and Excel the same?

#8

Not knowing for sure. At least I assumed that the same named functions in excel and libreoffice work the same. But it seems there are subtle differences.

#12

Have you tested this formula in Excel? Or only in Calc? Perhaps Excel will give the same error? In this case, it makes sense to carefully look at the piece of code that writes the formula to the cell - a typo?

#13

I didn’t find any sophistcated python package to create a .ods file. That’s why I used xlsxwriter.

#14

Being on Linux I have to admit I don’t have a Windows and also no Excel. I have to ask a friend of mine who has both to load my document. :slight_smile:

#15

I do not consider myself an expert in this area. Perhaps @Ratslinger or @karolus will join the discussion now and the problem will be resolved. But keep in mind, the first thing they ask for is the code.

#16

Exactly that…?

maybe: https://github.com/pyexcel/pyexcel-ods3

#17

:grinning_face_with_smiling_eyes: Of course not! Consider my bad English :nerd_face:

#18

This package doesn’t have enough features.

#19

Sorry, my fault, read: “That exactly is the question!”
Your English seems better than mine!

#20

Currently, a kind of solution (aka. workaround) is to save the created .xlsx file to .ods and all is ok. :slight_smile:

#21

@manfred.lotz, could you please upload a sample .xlsx file?

#22

I will create a minimal example, and then upload it. Make take some time.

#23

Just another programmer generating something he does not understand. Welcome to LibreOffice.

#24

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)

#25

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

#26

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.

#27

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

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.

1 Like
#28

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… :slight_smile:

Thanks a lot.

#29

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

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.