Issue 78030 - Name Define does not work across spreadsheets
Summary: Name Define does not work across spreadsheets
Status: CLOSED DUPLICATE of issue 4385
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: OOo 2.2
Hardware: All All
: P3 Trivial with 7 votes (vote)
Target Milestone: ---
Assignee: requirements
QA Contact: issues@sc
Depends on:
Reported: 2007-06-01 21:40 UTC by robertbradbury
Modified: 2013-08-07 15:12 UTC (History)
1 user (show)

See Also:
Issue Type: FEATURE
Latest Confirmation in: ---
Developer Difficulty: ---

Excel file with defines for physics constants and conversions (62.00 KB, application/
2007-10-16 13:32 UTC, robertbradbury
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description robertbradbury 2007-06-01 21:40:49 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:
(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.
Comment 1 keme6206 2007-10-15 13:11:39 UTC
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.
Comment 2 frank 2007-10-16 13:02:36 UTC

not a defect but a Feature request.

Could you please refine your description, as it is now it seems to complicated.

Comment 3 robertbradbury 2007-10-16 13:32:46 UTC
Created attachment 48935 [details]
Excel file with defines for physics constants and conversions
Comment 4 robertbradbury 2007-10-16 13:53:56 UTC
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:
Instead I type

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.
Comment 5 msqrd 2008-08-21 19:07:24 UTC
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. 
Comment 6 ooo 2008-08-28 11:50:08 UTC

*** This issue has been marked as a duplicate of 4385 ***
Comment 7 ooo 2008-08-28 11:50:56 UTC
Closing dup.