Description: In my line of work, I have to produce documents that have complex calculations about component parameters. In many cases, I would like to copy some tabs to an other spreadsheet to do the calculations for a new product. But where a cell's formula uses cells from other sheets, they will be augmented with file refeneces to the source file in the new file. And many times not just once, makes a formula extremly long. This even happens where the target file has the same tabs. There is only one solution, I have to use find and replace on formulas to remove all file references. 'file:///D:/....'# So it would be good to have an option like "do not change formulas" to the window where you copy a tab to an other spreadsheet. Some other people with the same issue: https://ask.libreoffice.org/t/formulas-move-and-copy-in-another-file/99999/2 https://ask.libreoffice.org/t/calc-copy-formulae-without-adjusting/22474 Steps to Reproduce: 1.On spreadsheet1, create two tabs. Do some calculations on on of the first sheet that uses a cell from the second sheet. 2.Copy first sheet to an other spreadsheet. 3.Check the formulas on the new spreadsheet Actual Results: Formulas that reference on cell(s) of an other tab on the source spreadsheet got file path referenced to the source file. Expected Results: Have a checkbox or something to disable formula augmentation. Reproducible: Always User Profile Reset: No Additional Info: Version: 7.6.6.3 (X86_64) / LibreOffice Community Build ID: d97b2716a9a4a2ce1391dee1765565ea469b0ae7 CPU threads: 12; OS: Windows 10.0 Build 19045; UI render: Skia/Vulkan; VCL: win Locale: en-US (hu_HU); UI: en-US Calc: CL threaded
It has to be that way. If a reference references another sheet, copying only the referencing sheet into another document has to create such external reference, otherwise it would be broken, pointing to an arbitrary other sheet or resulting in #REF! in case an accordingly positional sheet does not exist. You can however copy _both_, the referencing and the referenced sheets, _all at once_ (i.e. multi-select the sheets before copying), then the references are kept intact and still will point to the sheet(s) copied along.
(In reply to Eike Rathke from comment #1) > It has to be that way. If a reference references another sheet, copying only > the referencing sheet into another document has to create such external > reference, otherwise it would be broken, pointing to an arbitrary other > sheet or resulting in #REF! in case an accordingly positional sheet does not > exist. You can however copy _both_, the referencing and the referenced > sheets, _all at once_ (i.e. multi-select the sheets before copying), then > the references are kept intact and still will point to the sheet(s) copied > along. But the referenced sheet(s) are already there in the target file. That's why I can fix this by manually remove the file path strings from the formulas using find and replace. But it is a quite tediuos process... From my point of view, I can fix any missing reference later, that's why I suggest a checkbox or something to Calc do not touch the formulas.
*** Bug 160928 has been marked as a duplicate of this bug. ***
(In reply to Eike Rathke from comment #1) > It has to be that way. If a reference references another sheet, copying only > the referencing sheet into another document has to create such external > reference, otherwise it would be broken, pointing to an arbitrary other > sheet or resulting in #REF! in case an accordingly positional sheet does not > exist. You can however copy _both_, the referencing and the referenced > sheets, _all at once_ (i.e. multi-select the sheets before copying), then > the references are kept intact and still will point to the sheet(s) copied > along. If it is an option with the default being the current behavior then referencing a not existent sheet is not an issue. The values are copied. But sometimes the user wants the formulas, even if broken. They will fix them (add sheets, point them elsewhere, etc.) In such case, having a broken link is helpful. Makes them easier to spot. Break Link dialog option proposal (Menu: Links to External Files...) Replace with source document: O Values (default) O Formulas (value if local source not exist) O Formulas (forced) Being an option should take care of the case where the local source (sheet) does not exist.
Hi! I agree. I have this issue regularly. A popup window would be the proper way to to let the user handling these situations. At least, it would be nice to warn the user if the copied sheet contains external references from the destination document's point of view.
So is this issue will remain unsolved?