External reference compatibility between Excel and Calc - Err:509

[kapitanluffy]

Hello, I have files are made from ms excel and when I open it I got error 509. I tried manually fixing the referenced values from the files I saved it closed the file reopened it and got the same error again

In the screenshot the highlighted cell in the left (as far as what I understand in the formula bar) is pointed to the highlighted cell in the right. It shows error 509. I tried manually pointing it again by putting this in the formula bar

='file:///C:/root/shared/FS/SFC Comparative.xls'#'SFP 2012'.C9

It shows the correct value, I save it closes the file and when I open it it shows error 509 again

[oweng]

Please edit your question to provide an example, indicating the original Excel formula and the converted LO formula. Error 509 is a missing operator. Thanks.

[oweng]

Thanks for clarifying your question with some screenshots. In this case Err:509 is resulting from the reference to the external data source (separate spreadsheet) being malformed. This is probably due to the sheet name missing a leading absolute reference qualifier (dollar sign). The general form of an external reference is:

 'file:///path/to/file.ods'#$Sheet1.A1

Because the file name is always surrounded in single quotation marks it can include spaces, but for a sheet name containing spaces:

 'file:///path/to/file.ods'#$'a b'.A1:A2

… is required. In both cases though a leading dollar sign is included by default.

[Akos1]

Hi, I have the same issue. Tried to leave spaces from the name of the file and sheet as well. The problems remains the same.
When entering the link to the cell, the $ sign is included by default just as you mentioned. But after saving, closing and opening the file, the $ sign is found disappeared.
I’m wondering if long file names can be a cause…

Thanks,
Akos

Added some example below…

working link when added:
=‘file:///media/akos/UBUNTU_ADAT/DayMed/Megbizasi_dij_koltsegek_Akos_uj_140128.xlsx’#$OSSZESITES.C3

broken link after saving-closing-reopening:
=[‘file:///media/akos/ubuntu_adat/daymed/megbizasi_dij_koltsegek_akos_uj_140128.xlsx’]osszesites!c3

[oweng]

@Akos1, your example illustrates a different issue. Please ask a separate question. Thanks.

[Akos1]

Thanks your feedback, oweng, but I guess I have the same problem: LibreOffice malform the external reference, as I have shown.

[votedave]

Hi I am having the same issue.
I have several workbooks with multiple sheets that tally to an outcomes page.
I have now created a new document that summarizes the data from outcomes page of each book.
I entered all the links using = then referencing the cell within each book.
It worked initially, all the figures came through fine. Saved exited.
Next day re-open file and all I have instead of data is columns of Err.509.
Strangely the totals row I created summing across still display the correct value.
??help

[nicklear]

Also have this problem, when using the .xlsx format. I can solve the problem by saving as .ods, then the cell updates fine after closing and re-opening. But I have started using .xlsx so that I can edit files on my Android phone (using Excel). So for me this is a bug, but maybe it’s too much to ask for Calc to get this right when working in a non-native format?