Highlighting a named range does not work correctly

Let’s go back to my example, which was successfully corrected by @erAck (attached here).

players-and-points.ods (36.4 KB)


  1. Why are all ranges displayed in the “Name Box” window list, and not only those with the global scope and local to the active sheet? Why all? Can it be customized? In any case, in Excel, names from other sheets are not shown. Excessive information is harmful, but here it clearly interferes.
  2. Selecting an appropriate range name in the “Name Box” that belongs to the active sheet does not correctly highlight the range. Does not work. But it should (works correctly in Excel). All ranges are configured using mixed references, which must be calculated relative to the active cell. But when you open the “Manage Names” dialog, all references are calculated correctly. And why is it not selected when necessary? Try it yourself.

Select the ranges of the active sheet from the “Name Box”, for example: Players (selects the first row correctly), Points (selects the wrong range, not the current row but again the first row), Winners (selects the wrong range).

Press Ctrl+F3 after this. Name Winners (Team1) is broken.

Initially, Name Winners is $Team1.$B$2:$B2. What is meant?

Yes, it’s broken. But it doesn’t matter. Not affected by proper selection. The reference will be restored.
Means that there are no winners yet (the list is empty). Select the cell below and check the reference again. The list (range) of winners will expand. From cell $B$2 and down.

To demonstrate your effect, it is enough to define the name MyName as $Sheet1.$A$1:$A1 in a new Calc document.

The fact that in Excel, when choosing a name from Name box, the active cell is taken into account for me is new.

@sokol92, this is how it is calculated correctly in the “Manage Names” window relative to the active cell, this is how it should be highlighted correctly when you select it in the “Name Box” window. And if the references are relative, then the countdown from the active cell will be natural. How else?
Not critical. But a little unfinished and a little with errors.
I was not too lazy and checked this in Excel. Everything is consistent and logical.

Because you can use all in a formula expression. You can also use it to jump to fixed named ranges on another sheet.

Works for the Players ranges. It does not work for the Points and Winners ranges because they use relative row numbers, so there is no fixed range to jump to.
(which could be enhanced to take the current cell cursor position into account and calculate the resulting range before selecting it, at least if on the same sheet).

@erAck, yes, but that’s exactly how it works in Excel. What are we going to stick to?

What do you mean? The relative references in a named expression work the same as in Excel. (maybe except selecting the current resulting range as I mentioned).

I mean, it works in Excel, but in Calc, according to your words, it shouldn’t work.

“…there is no fixed range to jump to”.
And it is calculated correctly in the “Manage Names” window.

I did not say it shouldn’t work. I said it doesn’t work.

And how to qualify this feature? Is it a bug?

RFE, Request For Enhancement.

Thanks. I hope this discussion was helpful.

It could be if you now created such RFE in the bug tracker and linked back to it here.

RFE is filed

1 Like

Fwiw, fix commited for 7.2.3