[Solved] Error 509 for cell reference on other spreadsheet

Discuss the spreadsheet application
Post Reply
clamorousreed
Posts: 2
Joined: Tue Apr 25, 2017 7:58 am

[Solved] Error 509 for cell reference on other spreadsheet

Post by clamorousreed »

I have recently updated to apache office 4.1.3 on Window 10 home. I had a complex series of spreadsheets built on previous version of office. One spreadsheet links to the others pulling information in. I simply used the "=" and the cell reference on the other spreadsheet to pull the information in. Now I get Error 509. The cell I wish to use on the other sheet is a complex formula; =IF(COUNTIF(H4:I4;"a")>0;"a";0), H4 and I4 are also formulae: H4 is =IF(October.AL5<1;0;"a"), October being the Spreadsheet name, I4 is =IF(November.AK5<1;0;"a"), to complicate it further October.AL5 has the formula =COUNTA(F5:AJ5) where F5:AJ5 are values, November.AL5 similarly is =COUNTA(F5:AI5). Previously this worked beautifully I have renamed all the files and moved their location did this cause the problem? I have tested just using "=" with a value rather than a formula and that comes up with the same error - 509. Should I just reload 4.1.2? Please help it used to be such a cool spreadsheet updating without issue. So in summary the cell I wish to use has a formula that links to other cells with formulae which in turn link to a cell with a formula! Honestly is used to work
Last edited by MrProgrammer on Wed Nov 18, 2020 6:43 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OpenOffice 4.1.3 on Windows 10 Home
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Error 509 when using cell reference on another spreadshe

Post by RusselB »

This would be a lot easier to diagnose if you could upload a copy of your spreadsheet for us to analyze.
See [Forum]How to attach a document here for details regarding attaching a document and how to handle data that is confidential
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
clamorousreed
Posts: 2
Joined: Tue Apr 25, 2017 7:58 am

Re: Error 509 when using cell reference on another spreadshe

Post by clamorousreed »

I have since tested this further. I created a new file with some numbers in cells, saved it as 'Test' then opened a new spreadsheet and then used "=" to bring in the value from one of the cells in spreadsheet 'test'. Once again error 509. Apache office will not let me reference cells in other spreadsheets. I have reloaded the software, loaded an old version all do the same. All this after updating. Thanks
OpenOffice 4.1.3 on Windows 10 Home
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Error 509 when using cell reference on another spreadshe

Post by RusselB »

From the help file
509
Missing operator
Operator is missing, for example, "=2(3+4) * ", where the operator between "2" and "(" is missing.
Without seeing exactly what you are doing, thus my earlier suggestion of having you upload a copy of your spreadsheet, it is (realistically) impossible for us to diagnose what is going wrong.

One option that you might want to try, aside from what I already suggested, is to reset your user profile, per the instructions at viewtopic.php?p=58403
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
leprechaun007
Posts: 1
Joined: Sat May 20, 2017 3:13 am

Re: Error 509 when using cell reference on another spreadshe

Post by leprechaun007 »

I am experiencing the same issue and have attached a copy of the document. Let me know if there is any further info I can provide and thanks in advance.
Attachments
Tenn16 recipe list.ods
(26.18 KiB) Downloaded 107 times
Open Office 4.1.3
OSX 10.12.5
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Error 509 when using cell reference on another spreadshe

Post by RusselB »

Difficult to be sure, as the first problem I encounter has to deal with the fact that the formula references another spreadsheet, which (presumably) is located at the file location specified.
I don't have that file, so I can't check it.
That aside, the usage of the ' in the filename creates difficulties due to the fact that OpenOffice uses the same character to encompass the entire file address.
This problem occurs due to the ' in benton's
Thus, (if the above seems confusing), OpenOffice is seeing 'file:///users/km7/google drive/kitchen/costing and inventory/recipes/benton' for the file, not 'file:///users/km7/google drive/kitchen/costing and inventory/recipes/benton's bbq.ods'
If you manually typed in that address, then you could try changing it to ''file:///users/km7/google drive/kitchen/costing and inventory/recipes/benton's bbq.ods' (add another ' in front of file)
Another option would be to use a different file name that doesn't contain the '

I suspect that the mismatch of the ' (there needs to be a multiple of two) is your specific problem.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Error 509 when using cell reference on another spreadsheet

Post by MrProgrammer »

leprechaun007 wrote:I … have attached a copy of the document.
Your attachment shows that the error is due to a formula which references an external file with an apostrophe in the name ='file:///users/km7/google drive/kitchen/costing and inventory/recipes/benton's bbq.ods'#sheet1.b5. The first thing to do is open (File → Open) benton's bbq.ods, look at cell B5, and ensure the cell is not producing Error 509. If it is, fix it and save/close the file.

In theory ='file:///users/km7/google drive/kitchen/costing and inventory/recipes/benton''s bbq.ods'#sheet1.b5 should work. Note that you put two typewriter apostrophes (not a quotation mark) after benton and before s.

Or try =DDE("soffice";"/users/km7/google drive/kitchen/costing and inventory/recipes/benton's bbq.ods";"#sheet1.B5") or rename the file to avoid the apostrophe in the name.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Post Reply