Bug 35188 - cell reference to external spread sheets yields #NAME? rather than displaying the content
Summary: cell reference to external spread sheets yields #NAME? rather than displaying...
Status: RESOLVED INVALID
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Spreadsheet (show other bugs)
Version: 3.3.2 release
Hardware: x86 (IA32) Mac OS X (All)
: highest critical
Assignee: Not Assigned
QA Contact:
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-03-10 15:28 UTC by mrh
Modified: 2011-12-22 05:50 UTC (History)
2 users (show)

See Also:
i915 platform:
i915 features:


Attachments

Description mrh 2011-03-10 15:28:57 UTC
Standard spreadsheet capability is to be able to reference data cells in external spreadsheets. 

Behaviour: create a test.ods sheet with some data values. save the file. Create a second new spread sheet. Use any cell in the second spreadsheet and pick a reference a cell that holds a value from the first sheet. E.g. cell A1 in test.ods holds the value '3'. Set cell A1 in the second spread sheet equal to cell test.A1 (ie ='file:///[location]/test.ods'#$Sheet1!A1'). The result should be '3' in the second sheet.

The results: The cell result holds '#NAME?'. In the file descriptor, any caps are reduced to small letters. The '$' is dropped. 

This occurs with 3.3.1.2 OOOm19(Build:8) Ubuntu package 1:3.3.1-1ubuntu3-maverick.

The reason for major is that when previously created files are opened that have external links, any cells with references became invalid. Hence, the spread sheets become unusable with this version.

I've tried this with a 3.4 snapshot running on Ubuntu marverick and  OOO330m19 (Build:6) tag libreoffice-3.3.0.4 running on Fedora 14. Neither exhibit the problem.

Also the problem does not occur in 3.3.1.2 OOO330m19(Build:8) tag libreoffice-3.3.1.2 running in Windows 7
Comment 1 Kohei Yoshida (inactive) 2011-03-21 18:06:56 UTC
Not reproducible.
Comment 2 zeonchameleon 2011-04-29 04:12:20 UTC
I have similar problem.
References to external spreadsheets don't work with 'Index()' command.

For example, I have a file 'Calc.ods' on a local drive. The cell A1 holds '=INDEX('file:///Users/kosvlad/Downloads/Price-list.ods'#$Price.A1:D10;1;1)'. So, the 'Price-list.ods' is placed on a local drive too. Its name and its sheet name contain latin characters only without spaces.
The A1 of 'Calc.osd' returns 'Err:502'.

When I open this file in Open Office 3.2, it works properly, so there is no errors in formulas. Even if external 'ODS' file is placed on a file server and its full name contains cyrillic characters.
I tested localised Libre Office (RU) and I have the same problem.

Mac OS 10.6.7

LibreOffice 3.3.2 
OOO330m19 (Build:202)
tag libreoffice-3.3.2.2
Comment 3 tomraymondtom 2011-06-13 12:04:21 UTC
This works in Windows 7.
create a test.ods sheet with some data values. save the file. Create
a second new spread sheet. Use any cell in the second spreadsheet and pick a
reference a cell that holds a value from the first sheet. E.g. cell A1 in
test.ods holds the value '3'.
 To set cell A1 in the second spread sheet equal to
cell test.A1
1. In test, name cell A1 as a range and save it.
2. In the second sheet, select any cell.
   A. Under menu Insert select "Link To External Data.
   B. From the drop down menu, select test.ods
   C. Press OK
3. The result should be '3' in the second sheet.
Comment 4 Eike Rathke 2011-08-22 16:57:53 UTC
(In reply to comment #0)
> Set cell A1 in the second spread sheet equal to
> cell test.A1 (ie ='file:///[location]/test.ods'#$Sheet1!A1').

When using Calc A1 syntax the sheet separator is a '.' dot, so the correct formula would be ='file:///[location]/test.ods'#$Sheet1.A1
And this works in 3.4.2 and 3.3.4

The Excel A1 syntax (which uses '!' exclamation mark as sheet separator but again is different from the example you gave) however seems to be broken in both, 3.4.2 and 3.3.4
Comment 5 Rainer Bielefeld Retired 2011-09-10 01:49:55 UTC
It seems that the problem never has been reproduced, only user errors with wrong syntax!

@mrh / others:
Please feel free to reopen this bug if you find out that the problem still exists with the current stable LibreOffice version and if you can contribute requested additional information due to <http://wiki.documentfoundation.org/BugReport> (especially BugReport Details), especially a test kit
Comment 6 Björn Michaelsen 2011-12-22 05:50:19 UTC
RESOLVED, FIXED or CLOSED bugs cant be KEYWORD NEEDINFO.


Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.