Ask Your Question
0

External reference compatibility between Excel and Calc - Err:509 [closed]

asked 2014-01-29 02:17:15 +0200

kapitanluffy gravatar image

updated 2014-01-30 05:16:44 +0200

oweng gravatar image

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

image description

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-19 05:46:25.995908

Comments

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 gravatar imageoweng ( 2014-01-29 02:30:44 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2014-01-30 02:43:22 +0200

oweng gravatar image

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.

edit flag offensive delete link more

Comments

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

Akos gravatar imageAkos ( 2014-02-21 10:44:45 +0200 )edit

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

oweng gravatar imageoweng ( 2014-02-22 02:54:15 +0200 )edit

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

Akos gravatar imageAkos ( 2014-03-07 14:11:37 +0200 )edit

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

votedave gravatar imagevotedave ( 2014-05-29 07:17:38 +0200 )edit

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?

nicklear gravatar imagenicklear ( 2015-08-21 22:18:09 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-01-29 02:17:15 +0200

Seen: 3,393 times

Last updated: Jan 30 '14