Formula with named cell (from "label range") cannot be copied vertically

[rntri]

When I use “cell names” in a formula in LO-CALC, i can copy this formula horizontally without the cell name changing, but if I copy it vertically, then the names change to the names of the cells below.

Steps to reproduce:

1. Enter text “speed” and “Stop_dur” in cells A1 and A2

2. Enter 50 and 10 in cells B1 and B2

3. In LO-Calc, select “sheet”-> “Named Ranges and Expressions” → “Labels”

4. Add Label Range “$Tabelle1.$A$1:$A$2” and “For data range” = $Tabelle1.$B$1:$B$2

5. Enter formula “=B7/‘speed’60+B8’stop_dur’” in Cell B9

6. You can then copy it horizontally into C9 and D9 (green cells in the screenshot) and correctly get
   "=C7/‘speed’60+C8’stop_dur’ " and "=D7/‘speed’60+D8’stop_dur’ ", respectively

7. If you enter the equivalent formula "=B13/‘speed’60+C13’stop_dur’ " into D13, and try to copy it downwards to D14 and D15 (pink cells in the screenshot), however, the references ‘Speed’ and ‘stop_dur’ will not remain unchanged, but are changed to the cell names of the cells below them!

Here are the formulas in the cells after copying:

D13: =B13/‘speed’60+C13’stop_dur’
D14: =B14/‘stop_dur’60+C14’names’
D15: =B15/#NAME?$Tabelle1.$A360+C15#NAME?$Tabelle1.$A4

Obviously, the fields ‘speed’ and ‘stop_dur’ from the first line are replaced with the names below these definitions in column A2:A4!

Since this works fine horizontally, this is probably not intended?

Or do I miss some special characters to make the cell references explicitely absolute?

Screenshot for illustration:

I have tried this with the same error on these LO versions: 7.5.8.2, 24.8.6, 25.2.2, all on an Arm-Mac (MacOs 15.4).

I have attached the ODS-spreadsheet with the demo above:

error when copying formulas with named cells vertically.ods (52.5 KB)

Thanks for any hints if I am just doing something wrong!

[mariosv]

The label range is defined to contain “column labels”, so dragging in rows adapts the label to search for.

[rntri]

Thank You, Mariosv, this works great for the example given, i.e. if formulas containing constants only need to to be copied vertically:

However, I was probably trying to use “Defined Label Range”:
sheet→Named Ranges and Expressions → Labels
in an unintended way.

The idea was to have a neatly ordered collection of constants at the begin of the spreadsheet, and then to refer to them in formulas by the “name” (or, more concisely, “symbol”) in the first column.

If they are used in two-dimensional tables, then the formula in the top left corner needs to be copied e.g. horizontally, and then the first row vertically:

This does not work, and I have not found a nice solution for it.

A possible Workaround would be to use fixed Cell names for Cells B47 to B50, assigned in the cell-name entry field in the top left of the Libreoffice window (below font selector box). Then using the constants would work as desired:

With carefully named constants, the formulas (yellow/lightgreen/green above) are more or less self-explanatory in case you need to change something after a year, or want to give the spreadsheet to someone else.

Downside:

• You need to manually make sure the names in column A (e.g. A49= “f_speed”) match the internal names for col. B (e.g. B49=“f_speed”), otherwise the user may get confused

• You seem to be able to assign multiple Names to the same cell !?

• You cannot change the name of the cell afterwards

This problem would be greatly reduced if there was a function that could extract the cell name “f_speed” from Bx and automatically display it in Ax.

Does anybody know of such a method, maybe as an undocumented parameter to the function CELL(): Libreoffice-Docs for Function CELL()

What do other Libreoffice users consider “best practice” to make a spreadsheet easier to understand for other people that might need to use or tweak it?

Edit: .ods with examples above:
Workarounds.ods (128.1 KB)

[fpy]

@rntri

parameter to the function CELL():

although names apply to ranges.
rabbit hole …
55737 – Names of cells when clicking on it
156673 – Merged cells defined name display and creation differs in version 7.x to previous versions
166400 – [UI] Single cell as a range selection should not behave as a range for data entry

[Regina]

@rntri

The idea was to have a neatly ordered collection of constants at the begin of the spreadsheet, and then to refer to them in formulas by the “name” (or, more concisely, “symbol”) in the first column.

You do not need a label but a named expression: menu Sheet > Named Ranges and Expressions > Define
NamedExpression.ods (28.1 KB)

For a single cell you can mark the cell and then enter the name in the name field as well. And when a cell has a name assigned, you see its name in the name field, when the cell is active.

[rntri]

55737 – Names of cells when clicking on it

In his above post from 2012, Roman Eisele suggested:

1) If you enter a formula and click on some named cell, LibreOffice inserts
the address of the cell you clicked on into the formula;
but it would be preferrable that it inserted the name of the named cell
instead, just like Excel does.

Maybe it would be possible to use a modifier, like “shift-click”, “Ctrl-Click”, Middle-Mouse-Button, etc., to modify the behaviour from “Insert address of clicked-on cell into formula” to “Insert name of clicked-on cell into formula”?

This would not change any prior functionality and not endanger Excel-compatibility, but improve the ease of use considerably.

When using named cells in the current implementation, you need to memorize the name of each named cell, as it is only displayed when you click on it.

If, as a medium-savvy user, you have figured out that named cells exist, then it is easy to use e.g. right-click to select them for your formula.

[rntri]

Thanks, Regina, for your suggestion!

I have tried that, but I had come across two problems:

1. Suppose I want to use “slope” and “intersection” in a new formula in cell B5.
   I click on C3. enter “=” and then have to type “slope*A5+intersect” manually

It would be much more intuitive to enter “=” and then click on B2 and have “slope” inserted automatically. As described in the post above, which i composed while yours was posted, it would be nice if could use “right-click” or “shift-click” to to insert the “name” and the normal “left click” to insert the address.

2. In your NamedExpressions.ods, the actual name of the cell “B2”, “intersect” and the text in cell A2, “intersection” differ!

I fell into the same trap in my “Workarounds.ods” at least twice! It would be nice if there was a function that I could enter in cells A1 and A2 that would retrieve the names from cells B1 and B2 and display it without the chance of introducing an error. Maybe that would be possible with a macro?

[Regina]

@rntri

It would be much more intuitive to enter “=” and then click on B2 and have “slope” inserted automatically.

Yes, Excel has this feature. Request for LibreOffice is in tfd#64228. Unfortunately, it didn’t attract any developers.