Issue 38839

Summary: Inserting sheets from other files doesn't cope if the new sheet references other ones in the linked file
Product: Calc Reporter: malvineous <malvineous>
Component: editingAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: issues
Version: OOo 1.1.3   
Target Milestone: ---   
Hardware: PC   
OS: Linux, all   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---

Description malvineous 2004-12-11 10:28:58 UTC
If you insert a sheet from another workbook/file (as a link) and that sheet
references sheets in the other file, you get reference errors presumably because
OOo can't find the cells the newly imported sheet is referring to (because
they're in the other file.)

For example:

Sheet1.A1: =Sheet2.C5
Sheet2.C5: 1234

You should now see the number 1234 in the first cell of Sheet1.

Import Sheet1 from one.sxc (as a link)

Sheet1 in two.sxc should now be identical to Sheet1 in one.sxc.  You would
expect to see the number 1234 in cell A1, but you don't, because A1 references a
cell in the other file and it's not read in.

OOo should either (a) adjust the references in the newly added sheet to contain
the filename so that they still refer to the data in the linked file (even
though there's no sheet for that data in the current workbook), or (b) leave the
references alone so they'll work if you also import the other referenced sheets
under the same name.

IMHO, option (a) is better because you only need to insert sheets for the data
you want, and if those sheets refer to data in the original file that's fine,
you don't have to worry (or know) about it.  IIRC this is how Excel does it. 
Something like "=two.sxc!Sheet2.C5"
Comment 1 frank 2005-01-10 12:30:22 UTC

could not reproduce.

Using '=' in the new document and clicking on Sheet1.A1 in the first doc results
in a correct link with Filename.

Using Insert Sheet from file gives the reference based on the second document
which must be the case because the 2nd document can't know about the link in doc 1.

This step is not possible in Excel so it is a feature of OOo and will probably
not changed.

Using insert Object from File works in both applications in the same way.

So I close this Issue as worksforme.

Comment 2 frank 2005-01-10 12:30:46 UTC
closed wfm
Comment 3 malvineous 2005-01-23 02:15:58 UTC

I think you might've misinterpretted my explanation - it does work if you type
'=' and then click on a cell in another file (and this is the way I was hoping
it would work) however if you've made inter-sheet references in one file, and
then import one of those sheets into another file, all the references change to
#REF instead of the original document's filename - but sometimes they also
change to apparently random sheet names instead.

It does however seem that once you've typed '=' and clicked in the other file
suddenly it all starts working the way it should.  Except that I seem to have
stumbled across another bug, in that if the reference is to Doc1#Sheet2 after
the import it changes to Doc1#Sheet1.  I think the following steps should
reliably reproduce these problems:

1.  Close all instances of OOo
2.  Open Calc with a new spreadsheet
3.  In Sheet1, cell A1, type "=Sheet2.C5"
4.  Go to Sheet2 and in cell C5 type "1234"
5.  Go back to Sheet1 and cell A1 should now contain the value 1234.
6.  Save the document as one.sxc
7.  Create a new spreadsheet.
8.  Close one.sxc.  You should now have only one OOo window open, a blank
9.  From the Insert menu, choose "Sheet..."
10.  Select "From File" and Browse to select one.sxc
11.  Select "Sheet1" in the list, make sure the "Link" checkbox is ticked, and
click OK.
12.  You will now see a new sheet called "Sheet1_2" and in cell A1 it contains
the formula "=Sheet1.C5" which is incorrect - not only should it really be
Sheet2 as you entered in the original file, but it should be
"='file:///..../one.sxc'#Sheet2.C5" to maintain the linked reference (which is
why you select Link in the first place when inserting the sheet.)

Hopefully these instructions will allow you to reproduce the problem.
Comment 4 frank 2005-01-27 11:43:22 UTC

not a bug. You have entered a relative Sheet reference which means take the
value of C5 of one sheet to the right as there is Sheet 2 located in the one.sxc

Nevertheless I reflag this as enhancement and re-assign it to requirements.

Comment 5 ace_dent 2008-05-16 00:39:52 UTC Issue Tracker - Feedback Request.

The Issue you raised is currently assigned to 'Requirements' pending review, but
has not been updated within the last 3 years. Please consider re-testing with
one of the latest versions of OOo, as the problem(s) may have already been
addressed. Either use the recent stable version:
or consider trying the new OOo 3 BETA (still in testing):
Please report back the outcome so this Issue may be Closed or Progressed as
necessary - otherwise it may be Resolved as Invalid in the future. You may also
wish to search for (and note) any duplicates of this Issue that may have
advanced further by checking the Issue Tracker:
Many thanks,
Cleaning-up and Closing old Issues as part of:
~ The Grand Bug Squash, pre v3 ~