Apache OpenOffice (AOO) Bugzilla – Issue 44292
Links to named cells or ranges in other files not possible??
Last modified: 2005-09-07 02:28:31 UTC
OpenOffice.org Calc lets you assign names to individual cells or cell ranges. Aside from making formulas easier to read, this feature allows you to still refer to the correct named cell even if rows or columns are inserted. Even after you insert or delete rows or columns, OpenOffice.org still correctly assigns the ranges identified by name. The defined name works for all sheets in a file, so for example if the name 'total' is defined as '$Sheet1.$A$1', then when you insert a formula in Sheet2 such as "=total" the result will be the number from the nmaed cell in Sheet1. All this is good and seems to work as it should. But what happens when I wish to refer to a named cell in another file? Calc allows me to reference a cell in another document. But the reference must be by address (e.g. ='file:///file1.ods'#$Sheet1.$A$1). Then if you edit File 1 and say add a row at the start, the reference in File2 will be wrong!!!!! Why do references to cells in another file not recognise names in that other file? e.g. surely it would be acceptable to reference the named cell like this: ='File:///file1.ods'#total ????? I have raised this issue previously for version 1.1.0, and was told it would be fixed in version 2.0. Has a decision been made not to allow reference to named ranges in other files? or is it still coming in a future version? regards,
Created attachment 23750 [details] Sample XLS and ODS files
After further testing, I find that one can refer to a named cell or range in another file if one uses DDE linking. The attached files give examples of various types. - a1.xls is a base spreadsheet with some data and a named cell called 'total'. - a1.ods is the same spreadsheet loaded by OOO and saved as ODS format. - a2.xls is a test spreadsheet that contains links to a1.xls. One link is a direct cell reference and the other refers to the cell name 'total'. Both links work in excel. - a2.ods is the a2.xls file as loaded by OOO. Note that the direct reference has been translated correctly, but the named reference gets an Err:508. - a3.ods is another test spreadsheet playing around with various types of links to a1.xls and a1.ods. Here you can see that a link "=DDE(..." works ok with either cell numbering or cell names. But you can also see that a link "='file:..." does not work with cell names. Two points emerge: 1) if you can refer to a named cell in another file using "DDE", then why not using "file:" ?? 2) if only DDE can be used to reference a named cell in another file, then maybe the excel import tool should convert such links in xls files to "DDE" links instead of giving an unacceptable "Err:508" message ?? There will always be those die-hards who insist on sticking with the expensive office products like excel. And those of us already converted to OOO will still need to be able to work with them in future, so little problems like this need to be ironed out soon. regards,
Confirming as a new issue.
double to Issue 3740 *** This issue has been marked as a duplicate of 3740 ***
closed double
Please see my comments against issues 3740 and 45705, I believe this is an important issue that needs fixing in version 2.0, and should not be left as some 'future enhancement'.