Cannot copy raw formulas linking to other sheets without referencing/linking to external document

English
,
#1

I am attempting to copy the raw formulas from one calc document to another with zero linking or referencing of anything in the original sheet. This should of course be possible with a special paste, and choosing to retain formulas with the checkbox. This works fine for formulas which aren’t referencing a different sheet within my document, but not for ones which do.

Here’s an example:

=SUMIF($'Sheet1'.Q:Q, "*String Here*", $'Sheet1'.L:L)

Let’s consider we have two separate LibreCalc documents, named Document1 and Document2. Each document has two sheets with identical names: Sheet1 and Sheet2. Inside Document1 on Sheet2, we have a cell containing the above formula, referencing some data on Sheet1.

I want to copy the above formula from Document1 into Document2, having the resulting paste reference the Sheet1 inside of Document2 and NOT from the original Document1. However when I attempt to do this in reality, I will end up with a very weird reference path, which also doesn’t function most of the time.

=SUMIF('file:///path/to/Document1.ods'#$'Sheet1'.Q$1:Q$1048576, "*String*", 'file:///path/to/Document1.ods'#$'Sheet1'.L$1:L$1048576)

This is the behavior when special pasting and choosing, “formulas” as an option, with linking off. Is this a bug, or am I doing something wrong?

Here’s my version information: Version: 7.6.4.1 (X86_64) / LibreOffice Community
Build ID: 60(Build:1)
CPU threads: 16; OS: Linux 6.6; UI render: default; VCL: kf5 (cairo+xcb)
Locale: en-US (en_US.UTF-8); UI: en-US
7.6.4-2
Calc: threaded

I’ve tested on two different systems, both probably with comparable builds and versions, and the behavior is consistent. Is there anything else I can do to help get this issue fixed, more info I can provide, etc?

Thank you for your time and help!

#2

If I wish to copy a formula, without altering it, I usually copy either the text in the formula-bar above the grid, or click in the activated cell, as I do when I modify a formula, then use Ctrl-a, Ctrl-c for copy.
.
I paste then usually also in the formula bar. Disadvantage: This works only for a single string/formula.

#3

Of course, but unfortunately, I am wishing to copy some ~100 cells and that isn’t a very satisfactory solution for me.

I really find it hard to believe that what I am trying to do isn’t a common use case.

#4

If it is cells down a column, the copied formula can be dragged down. If it is 100 different columns it will be more work, but there may be better options.

#5

Unfortunately it is a roughly square shape of cells. Each cell is also slightly different from each other in the specific data which is being referenced and searched for beyond just the cell offsets - the string is different for each row.

But I may keep that in mind for a backup workaround, thank you.

#6

Then try search and replace for your cells and replace the file:///- part with nothing in the selected area.

1 Like
#7

Do you need to have the sheet reference $‘Sheet1’ as absolute? Use ‘Sheet1’ and I think it solves your issue when copying.

3 Likes
#8

Note though that relative sheet references are preserved and when pasted also point to a relative sheet with the same offset in the target document. If the original formula cell resides in Sheet1 then the reference doesn’t need to explicitly state Sheet1 (i.e. the same sheet) at all.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.