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)