Apache OpenOffice (AOO) Bugzilla – Issue 38839
Inserting sheets from other files doesn't cope if the new sheet references other ones in the linked file
Last modified: 2013-08-07 15:12:27 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: one.sxc ~~~~~~~ Sheet1.A1: =Sheet2.C5 Sheet2.C5: 1234 You should now see the number 1234 in the first cell of Sheet1. two.sxc ~~~~~~~ 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"
Hi, 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. Frank
closed wfm
Hi, 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 spreadsheet. 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.
Hi, 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 file. Nevertheless I reflag this as enhancement and re-assign it to requirements. Frank
OpenOffice.org 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: http://download.openoffice.org/index.html or consider trying the new OOo 3 BETA (still in testing): http://download.openoffice.org/3.0beta/ 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: http://www.openoffice.org/issues/query.cgi Many thanks, Andrew Cleaning-up and Closing old Issues as part of: ~ The Grand Bug Squash, pre v3 ~ http://marketing.openoffice.org/3.0/announcementbeta.html