Apache OpenOffice (AOO) Bugzilla – Issue 78030
Name Define does not work across spreadsheets
Last modified: 2013-08-07 15:12:27 UTC
In Excel spreadsheets, it is possible to "Insert >> Name >> Define" to insert names which reference cells in other worksheets. This feature also works for cells in other spreadsheets using a slightly more complex syntax. The syntax appears to be of the form: ='X:\[spreadsheet.xls]sheetname'!$columnletter$rownumber or ='X:\[spreadsheet.xls]sheetname'!Definition (where Definition is a defined name in the spreadsheet.xls namespace) When opening a spreadsheet with an external name definition, calc does not complain about the use of external definitions, nor does it seem to attempt to locate the definition. (Under Windows, I think Excel has a dialog box which attempts to assist one in searching for missing spreadsheets. Under Linux, a logical alternative might be to search the PATH directories for any external spreadsheets. Or perhaps there is a completely separate OO search path that should be used.) I think under Excel under Windows, the search process for spreadsheets is something like: "Open spreadsheets; Directory of referencing spreadsheet; Dialog search box". In any case OO (calc) *should* produce an error for names which are defined in external sheets whose spreadsheet file or worksheet or cell cannot be located. OO appears to recognize that the sheet contains references to external files and asks whether the links should be updated, but the name definitions end up as "#NAME!Definition" even when the referenced spreadsheets are present in the directory of the referencing spreadsheet. It is not clear whether this is (a) because OO is not processing the ='Drive:[filename.xls]' portion of the name (which obviously needs conversion to UNIX format) or (b) whether it fails to properly perform the Definition (or cell) lookup in the filename.xls namespace. This is a significant problem for people working with a number of spreadsheets which have complex cross-spreadsheet name define references as it prevents them from directly transferring a set of spreadsheets from Excel to Calc. Presumably there has to be some cleverness in the name resolution process as one could imagine circular references that would put the lookup process into infinite loops. I do have available a complex set of spreadsheets which use this feature which can be used to test whether the problem has been fixed.
Confirmed using OOo 2.3 (Norwegian) on WinXP pro SP2. "Insert - link to external data" will use named ranges (and not cell references). Formulas referencing external data directly will not use defined names in the external workbook, but require a cell reference.
Hi, not a defect but a Feature request. Could you please refine your description, as it is now it seems to complicated. Frank
Created attachment 48935 [details] Excel file with defines for physics constants and conversions
This is not a "feature request" if you are claiming full Microsoft Excel compatibility. If you want to claim such compatibility and enable people switching from Excel to oocalc then its a requirement. I have attached a physics.xls Excel spreadsheet. There are a number of physics constants and conversion factors in it where the constant or conversion factor has an associated defined name, e.g. the conversion factor for calories to joules has a define "cal_to_J". The constant for the speed of light has two defines "LightSpeed" and "LightSpeed_m". When I use these constants in calculations other spreadsheets, I do not type: Physics.xls!Constants!C28 Instead I type Physics.xls!LightSpeed_m Oocalc does not perform the location of the file and lookup of the value for a define in any formulas involving Name Defines in "external" spreadsheets. As a result of this bug, I must still use Excel (under wine which tends to be problematic) instead of using oocalc for dozens of spreadsheets on scientific research projects I have initiated. Two other points in this to be concerned with... 1. In the past when working with Name Defines under Windows 2000 and Office 2000, it seemed as if the full path, e.g. C:\Username\My Documents\ExcelFiles\Physics.xls" may have been maintained within the file. Under Linux, one may want to truncate to the actual filename and search the directory of the referencing sheet, then perhaps either PATH or OpenOfficeFilePath (if such exists) to find the precise file. When Windows could not find the file a popup window would occur allowing you to browse for the file (only if one canceled the file lookup process would the spreedsheets display a cell error indicating the calculation had failed because the name lookup had failed). 2. The selection of a cell, e.g. Constants!C29 (Light Year (km)), pulls up "LightYear km" in the define name box. But the actual define name is "LightYear_km" as can be seen by hitting the define name box down arrow. I do not believe that Microsoft Excel removes the underscores from the name defines and I would consider it misleading to do so, as it would suggest that my external cell reference should be something like "Physics.xls!LightYear km" which would probably fail the lookup unless there is some code in defined name lookups that considers spaces and underscores to be equivalent characters.
I agree that inability to resolve external names is a deal killer for any serious Excel port. The file path issue should be irrelevant provided that the edit links dialog allows the source file location to be changed throughout the file.
Dupe. *** This issue has been marked as a duplicate of 4385 ***
Closing dup.