[Solved] C&P range of formulas not working LibreOffice Calc

Discuss the spreadsheet application
Post Reply
bill.hudacek
Posts: 3
Joined: Sun May 02, 2021 5:49 pm

[Solved] C&P range of formulas not working LibreOffice Calc

Post by bill.hudacek »

Searched help, this is not shown to be special case or a problem at all. Someone reported the same issue in 2010, that solution can't be used in LibreOffice, as far as I can see (see viewtopic.php?f=9&t=33387.

I have moved ~/.config/libreoffice/ to '.old', then restarted libreoffice. So completely vanilla config here.

[N.B. copy == "cut" or "copy" below, it doesn't matter which you try]

I select a range of cells, 1Rx3C, each has a formula that generates its value. Control-C to copy. Select a single destination cell. Control-V to paste brings up 'text import', as you'd expect. I can't get formulas to paste using this dialog - again, as you'd expect.

With the range is still selected, Control-Shift-V in a single cell to paste special, I can only see the simple dialog in attachment 2021-05-02-PasteSpecial_001.png. Source 'unknown', only selection is 'unformatted text'. That might be where the breakage is. I'm not sure.

If I use mouse, with range still selected, select a single destination cell, right-click, choose 'paste special->Formula', nothing happens! In fact, nothing happens even if only one cell (with a formula) is selected as the source.

The only way I'm copying formulas is to select a single cell, hit F2 to get at the formula, Control-A to select all, then hit ESC and move to another cell, and Control-V to paste. And none of the cell references (relative) are adjusted at all.

[FYI, fill right or fill down does work, but all relative cell refs are adjusted, of course, and since it's immediately to the right of the range, it's likely not why you are trying to do this.]

It just can't work this way by design. I know this has worked for me, hundreds or thousands of times (long-time user of OOO/AOO/LO here...). But right now, in my config, it's not working at all.

This 'use case' is the same as 'moving' formulas among worksheets, or even copying to paste into an external spreadsheet (though I may have only done that in Excel, I kinda remember being able to do it in OO before).

Anyone have any ideas? Many thanks in advance.
Attachments
2021-05-02-PasteSpecial_001.png
Last edited by bill.hudacek on Mon May 03, 2021 10:14 pm, edited 1 time in total.
LibreOffice Version: 6.4.7.2 on Fedora 32, x86_64
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: C&P range of formulas not working LibreOffice Calc

Post by Villeroy »

bill.hudacek wrote: I select a range of cells, 1Rx3C, each has a formula that generates its value. Control-C to copy. Select a single destination cell. Control-V to paste brings up 'text import', as you'd expect. I can't get formulas to paste using this dialog - again, as you'd expect.
No, there should not pop up any dialog when copying Calc cells to a Calc cell.
A text import dialog pops up when you copy data from a plain text editor. There must be something wrong with the installation.
Do you have some kind of clipboard manager up and running? Then stop this tool and see if it makes a difference.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
bill.hudacek
Posts: 3
Joined: Sun May 02, 2021 5:49 pm

Re: C&P range of formulas not working LibreOffice Calc

Post by bill.hudacek »

Villeroy wrote: No, there should not pop up any dialog when copying Calc cells to a Calc cell.
A text import dialog pops up when you copy data from a plain text editor. There must be something wrong with the installation.
Do you have some kind of clipboard manager up and running? Then stop this tool and see if it makes a difference.
Now this is interesting! This is linux, as I said, and I use the essential app "Parcellite". It stores hundreds of things I've copied, persists across login sessions.....it's amazing how productive you can be when you need to copy/paste like 3 things from one app into another. And it doesn't interfere with things like Shutter (that auto-copy an image to the X clipboard).

This is an easy one to test. Quit Parcellite, open up my spreadsheet, copy that column of cells that show values but are based on formulas (i.e., a range)............click on empty cell somewhere......paste......(drum roll.......) and - BOB'S YOUR UNCLE! Formulas are right there. Beautiful.

This was a fantastic intuitive pickup, @Villeroy, unless you've seen it before. <g>

Now, to figure out if/whether I can enable both to co-exist.

Hmmmm. <Alt>+<Shift>+F brings up a specific spreadsheet; maybe that shortcut should instead exit Parcellite, start LO, then wait for LO to exit, and restart Parcellite. That's single-tasking though, I often leave spreadsheets open all day. I think <Control>+<Alt>+<Shift>P to stop Parcellite, and <Alt>+<Shift>P to restart it might work nicely. Even while spreadsheets are open constantly. Brilliant!

Ah, well to find a solution was wonderful. I was really at my limit before I even posted here. I always solve my own problems, have for a long time - thus I'd never created an account here (though I have StarOffice, Apache OpenOffice and LibreOffice links for bug reporting :-) ).

Does this forum allow 'Solved' tags, or do you do Ubuntu-ish rename-the-thread? I'll look at that in a moment.

THANKS AGAIN. Hope this helps someone else.
LibreOffice Version: 6.4.7.2 on Fedora 32, x86_64
bill.hudacek
Posts: 3
Joined: Sun May 02, 2021 5:49 pm

Re: C&P range of formulas not working LibreOffice Calc

Post by bill.hudacek »

MrProgrammer wrote:Hi, and welcome to the forum.
Hi back at you, @MrProgrammer. I appreciate the kick-start of what would have been an exhaustive investigation - I mean that in a good sense, I've managed UNIX Systems Technical Support orgs and such is exactly the approach we'd use for PD/PSI (problem definition, problem solution identification) at Level 3 (in other words, IBM or AT&T or Data General or HP punted on the issue (!!!). It was the most fun I've had in my life (when talking about work, anyway).

Lucky for us both, intuition or experience led @Villeroy to nail it - first try!

I'll save this page both as memory aid for this issue and a guide for 'the next time'. Thanks for the links/refs.

And I'll try to watch what happens here just in case I can pay it forward.

Thanks to all who read this, or jumped in to help.

I'll rename the title per your survival guide - but - how can I at-sign another person so they get a notification (whether following a thread or not)? If it's personal pref (they might not follow a thread where they posted......because they don't want to be notified at all), then I get it.
LibreOffice Version: 6.4.7.2 on Fedora 32, x86_64
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [Solved] C&P range of formulas not working LibreOffice C

Post by RusselB »

how can I at-sign another person so they get a notification (whether following a thread or not)? If it's personal pref (they might not follow a thread where they posted......because they don't want to be notified at all), then I get it.

The current forum version doesn't allow this. If someone has replied to this topic and are still following it, then they'll be notified via e-mail that a new post has been made to the topic.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Post Reply