Apache OpenOffice (AOO) Bugzilla – Issue 62989
External references lose document URL when copied to another document
Last modified: 2014-04-02 22:34:59 UTC
If I use the <Edit|Sheet|Move/Copy Sheet> menu selections (or Rt-click on the tab and choose Move/Copy) and specify either Move or Copy to a different file (diff "workbook") either existing or "new document", it creates the sheet by gives an alert box with msg "The new table contains absolute references to other tables which may be incorrect." I have looked at all the "options" I know to find a place to change this behavior but don't see anything that looks like a possibility to change this. I am assuming that it is a bug, and if not this certainly isn't obvious at all. I have to "Find" <=$#REF!.> and "Replace" with <='file:///C:/emlab/office/emlab.ods'#$projects.> (angle brackets for clarity only). Please fix or explain (but it really isn't smooth or (shame on me) excel-like. Thanks
the warning message comes up when you have absolute references to other sheets from your copied/moved sheet, eg. A1=$Sheet2.A1 (in sheet 1); notice the $, they denote absolute references, so refernces which don't change when copied. As you copy/move only one sheet, the reference gets broken, because the reference shall not change but the referenced sheet isn't present in the new doc. Note you can move several sheets at once or move the sheets containing the referenced cells prior to moving the sheet with the absolute references; then the refernced sheet is present, and the absolute references will work and are not changed into #REF! I close this issue as invalid, feature is present and works as expected.
closing invalid
I don't understand why this should be considered correct performance. The reference I start with is a fully qualified reference to an existing workbook and sheet that do not exist in the source file from which I am Copying the sheet out to a new document. I am working in a file named "accounts.ods" and have the following reference: ='file:///C:/EMLAB/Office/EMLAB.ods'#$PROJECTS.$D$190 which is to a workbook that does not contain the referenced sheet (and it works fine), so why should a new file(workbook/sheet) with this full reference not also work? I think it should. I can do this from Excel 2000 and it is a very useful feature, but never mind that, why should the reference get hacked and not be copied as a fully qualified reference? Why wouldn't it copy the reference and use the reference to access the original data as it does in the accounts.ods file? This doesn't make any sense. Further if I Select All (of the sheet I want to "Copy" out to a new document, then create a new blank document and Paste the copied sheet into a blank sheet, it works fine: keeps the full reference and displays the linked data. I think the failure to do the same when I Move/Copy to a new document IS a flaw. Sorry. I'm not trying to cop an attitude. I appreciate all the work you folks do on Oo and I'm trying hard to get away from Megalimp products. Thanks
hm, copy and paste does not work for me, but Ok! the problem is that absolute references to different files sheets do not get copied/moved correctly. This is a defect as the referenced sheet exists at the given place; so confirming with 680_m158 on WinXP Pro SP2.
Hi Eike, please have a look at this one. Frank
Since references to sheets of external documents are implemented as references to an imported hidden copy of that sheet and its values, they are not copied along. Changing this implememtation to another type of external references would be a new feature => RFE, requirements.
This issue is still not resolved as of ver 2.2 As I use this feature daily to send out extracts of reports, I am forced to switch to Excel to do this. This feature is very useful for folks like me who generate lots of MIS reports. Pls shift the issue to higher priority and resolve.
Our users are having problems with this because they are used to Excel and think Calc is broken. It doesn't matter whether it's technically or semantically a bug or a feature request, it matters what the users think, and they think Calc is broken because it doesn't act like Excel. That may not be fair, but that's the way it is. Any chance of getting a target milestone on this? Thanks. BTW, Excel adds the reference to the original file in the formula of the copied file when copying. The only workaround I can think of is for the user to manually change all the formulas, and there are hundreds or thousands of them. Naturally, they are reluctant, especially since they are used to it working in Excel.
Any chance of setting a Target milestone on this?
@kohei: FYI, with the new external references the references copied to another document have an empty external source location and the formula results in #NAME? Same if a formula cell is copied via clipboard to another document.
Defect, not feature.
I'm taking it. So, basically external references lose their document URL when copied to another document.
It's already tight for 3.3 since the code freeze is around the corner, so probably for 3.4.
Will this be fixed now in 3.4 ? pls confirm
Reset assignee on issues not touched by assignee in more than 1000 days.
Any progress on this issue? Has this been addressed in a newer version? This may be trivial to some, but essential to others (such as myself...)
(In reply to robbk from comment #8) > Our users are having problems with this because they are used to Excel and > think > Calc is broken. It doesn't matter whether it's technically or semantically a > bug > or a feature request, it matters what the users think, and they think Calc is > broken because it doesn't act like Excel. That may not be fair, but that's > the > way it is. Any chance of getting a target milestone on this? Thanks. BTW, > Excel > adds the reference to the original file in the formula of the copied file > when > copying. The only workaround I can think of is for the user to manually > change > all the formulas, and there are hundreds or thousands of them. Naturally, > they > are reluctant, especially since they are used to it working in Excel. Well said. Thank you.