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

Discuss the spreadsheet application

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

Postby bill.hudacek » Sun May 02, 2021 6:16 pm

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 https://forum.openoffice.org/en/forum/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
bill.hudacek
 
Posts: 3
Joined: Sun May 02, 2021 5:49 pm

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

Postby Villeroy » Sun May 02, 2021 11:26 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 29722
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby MrProgrammer » Mon May 03, 2021 2:31 am

Hi, and welcome to the forum.

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. …. 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'. … 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.
I have no idea what you are trying to accomplish. It seems as if the method(s) you are using are not an appropriate approach. Explain your goal.
XY Problem
Explain the goal in English and attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Because you are using formulas, we need to be able to see them, and we need to know what cells they are in. The only effective way to provide that informaiton is to attach a document. Specify what range of cells you are copying. Specify what the target cell is. Specify exactly how and where you do your paste. There are multiple ways and we need to know what you are doing. Even better, also put the desired result in some range of cells in the spreadsheet. Just type it in. Tell us which range has this expected result. There is no way I can help you since I don't understand your goal.

bill.hudacek wrote:Control-V to paste brings up 'text import', as you'd expect.
I would not expect to see the Text Import dialog if you have cells on the clipboard.

bill.hudacek wrote:I can't get formulas to paste using this dialog - again, as you'd expect.
Calc cannot paste formulas unless you have cells with formulas on the clipboard. Attach a document demonstrating the difficulty. Specify exactly what you are copying and exactly how and where you paste. I will not help further without an attachment and a clear statement of your goal.

bill.hudacek wrote: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.
The appearance of this dialog suggests that there is text on the clipboard instead of cells. There is a ten-year-old issue where paste does not paste what you think it will. I don't know if it could be related to your difficulty.
OO does not paste the text contents of the Windows clipboard - instead, it pastes the previous text copied in OO

bill.hudacek wrote: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
When you open the cell with F2 and copy the formula's text, the clipboard contains text, of course, not a cell. Calc adjusts relative references only when you have cells on the clipboard. If you have text on the clipboard it is pasted as is.

bill.hudacek wrote:This 'use case' is the same as 'moving' formulas among worksheets, or even copying to paste into an external spreadsheet …
Copying and moving are different operations and you will get different results, in some cases quite a bit different.
[Tutorial] Formula Adjustments during Copy and Move

Villeroy wrote:No, there should not pop up any dialog when copying Calc cells to a Calc cell.
Here is one situation where a dialog can appear. I don't believe this is what the OP is doing, though. The process is quite helpful because it allows me to use the features of the Text Import dialog, and the only way I know to trigger the dialog with cells on the clipboard is via a macro. Long ago I recorded one, PasteUnformatted in [Tutorial] Favorite Recorded Calc Macros. When I copy cells in more than one row to the clipboard, then call PasteUnformatted, this triggers the Text Import dialog.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 4004
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

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

Postby bill.hudacek » Mon May 03, 2021 10:01 pm

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

Postby bill.hudacek » Mon May 03, 2021 10:13 pm

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
bill.hudacek
 
Posts: 3
Joined: Sun May 02, 2021 5:49 pm

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

Postby RusselB » Tue May 04, 2021 12:38 am

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.
User avatar
RusselB
Moderator
 
Posts: 6400
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: No registered users and 22 guests