Calc: Cutting and Pasting Ranges

users
#1

I had a troubling experience that I want to share.

Cut & Paste from a range of cells on one sheet (e.g. Sheet4.A1:AL418) to a location on another sheet (e.g. Sheet2.XA1) leaves formulas that reference a range of cells (e.g. Sheet4.P$3:P$1230) that is partially but not entirely within the Cut range as they were with no warning. This action left me scratching my head for a while. The examples are from real life.

The desirable action would be to issue a warning but I doubt that would be practical. In this case there were something in the vicinity of 16000 formulas that failed to get converted. Thank goodness for the ability to undo multiple steps.

#2

Many places I am told that inserting formula numbering is quite simple. Write "fn" in a new line and press the F3 key.
In my copy of LibreOffice 4.4.2.2 (Windows) I am not able to do this. Instead I get a message that LibreOffice did not find autotext for the short cut "fn" or something like that. (It works in my copy of OpenOffice 3.2).
  I think it has to do with some settings somewhere but am not able to find out where.
Some suggestions?

Kolbjoern

#3

@Kolbjørn,

A default user profile has a correctly defined AutoText entry for "Formula
Numbering" assigned the "FN" shortcut.

Suggest you clear your per-user profile and have another go.
https://wiki.documentfoundation.org/User_Profile

You could also verify the shortcut is assigned in a Writer module session
from the Edit -> AutoText menu dialog, or use its shortcut <Ctrl>+F3 to
launch. Expand the "Standard" section, and locate the "Formula Numbering"
entry. It can be edited from the E=mc^2 default--as well as adjusting the
numbering format.

Stuart

#4

I had a troubling experience that I want to share.

Cut & Paste from a range of cells on one sheet (e.g. Sheet4.A1:AL418) to a location on another sheet (e.g. Sheet2.XA1) leaves formulas that reference a range of cells (e.g. Sheet4.P$3:P$1230) that is partially but not entirely within the Cut range as they were with no warning. This action left me scratching my head for a while. The examples are from real life.

The desirable action would be to issue a warning but I doubt that would be practical. In this case there were something in the vicinity of 16000 formulas that failed to get converted. Thank goodness for the ability to undo multiple steps.

[Tutorial] Absolute, relative and mixed references

#5

Den 05.05.2015 18:48, V Stuart Foote skreiv:

@Kolbjørn,

A default user profile has a correctly defined AutoText entry for "Formula
Numbering" assigned the "FN" shortcut.

Suggest you clear your per-user profile and have another go.
https://wiki.documentfoundation.org/User_Profile

Thank you for answering.
I uninstalled LibreOffice a couple of days ago and renamed my user profile before reinstalling LibreOffice. And yes, I remembered to delete the quickstarter too. (I prefer to rename instead of adding a new profile).

You could also verify the shortcut is assigned in a Writer module session
from the Edit -> AutoText menu dialog, or use its shortcut <Ctrl>+F3 to
launch. Expand the "Standard" section, and locate the "Formula Numbering"
entry. It can be edited from the E=mc^2 default--as well as adjusting the
numbering format.

Stuart

Well, the only available file here is "My autotext".
Perhaps I have misunderstood something?
If I click on the button Autotext I can select Import …. But what to import and from where?

As I am a bit curious I tried this function on 3 different computers, same result: No autotext found for the shortcut "fn".

    LibreOffice 4.4.2.2 / Windows 7.
    LibreOffice 4.1.3 / Windows 8.1 (laptop).
    LibreOffice 4.3.4.1 / Windows 8.1

Kolbjoern

#6

Thank you Andreas.

Basically I was inadvertently attempting to do something I ought not to do. I was cutting and pasting from a range of cells (Sheet4.A1:AM418) to a different sheet. Some of those cells contained references to a PARTIALLY embedded range of cells (Sheet4.P$3:P$1230). After the cut and paste operation, the data from 416 of the 1228 referenced cells in the embedded range has been relocated while the data in the other 812 referenced cells remain where they were. LO Cannot be expected to read my mind WRT my intent in my internally inconsistent operation.

My only issue was that I was not alerted that I was making this error. When I saw that I was getting wrong results I undid the cut and paste. When I finally spotted what I'd done wrong I was able to revise the formulas by simply deleting rows 419 through 1230 before performing the cut and paste once again. In this instance that was the correct action to take.

I'm so happy that I spotted the incorrect values in calculated cells right away but it could have been disastrous.

Upon detection of this inconsistency I would have expected LO to have displayed a notification dialog with one option being to cancel the operation.

As I indicated, I made a major boo-boo. I doubt many others have done the same but I hope my experience helps somebody else,

#7

Here are the AutoText dialog panel you should see in your local, these for
en-US with 4.4.3.2 release.

<http://nabble.documentfoundation.org/file/n4147974/Edit_AutoText_dialog.png>

<http://nabble.documentfoundation.org/file/n4147974/Edit_AutoText_dialog_FormulaNumbering.png>

#8

When opening the AutoText dialog I only see "My AutoText".
In my AutoText folder (…\LibreOffice\4\user\autotext) there is only one file: mytext.bau.
Obviously something is missing, but what and how to get it?
Kolbjørn

Den 06.05.2015 00:13, V Stuart Foote skreiv:

#9

@Kolbjørn,

Not certain, but suspect that at least the "Standard" set of AutoText in
en-US will always be included in core build, and translated into the
language packs. You can check from Tools -> Options and resetting your user
interface and documents to English (USA) and restarting LibreOffice. Then
check if the "Standard" AutoText, which includes the "FN" formula numbering
table cell insertion, is available to you.

Then if you have set an interface language, it is Norwegian right? If so,
you would need to add the corresponding Language pack, either Bokmål (nb) or
Nynorsk (nn) as you prefer.

For 4.4.2.2 they would be here for the Windows builds:
http://www.libreoffice.org/download/libreoffice-fresh/?type=win-x86&version=&lang=nb
http://www.libreoffice.org/download/libreoffice-fresh/?type=win-x86&version=&lang=nn

For the 4.4.3.2 pre-release here
http://dev-builds.libreoffice.org/pre-releases/win/x86/

You can back up the tree to get other OS, or languages as needed.

Stuart

#10

SOLVED
After some hours of work the solution was, as often, very simple.
Stuart, your idea works :slight_smile: .

  * I added en-US as a user interface language, switched to this
    language and "fn - F3" worked .
  * Then I copied the .bau files from …LibreOffice4/share/autotext/en-US
    to the corresponding folder with my language.
  * Changed to Norwegian nynorsk. It now works as asumed.

Well, I first reinstalled the Norwegian language packages to give it a try. The fn - F3 combination still doesn't work.

Yes, you are right, I am using Norwegian nynorsk (and sometimes bokmål).
Thank you Stuart and Regina.
Kolbjørn

Den 06.05.2015 13:54, V Stuart Foote skreiv: