Named ranges - Labels - Reference lock? [Calc]

[davidmsousa]

Problem

I was trying to lock a label, much like you lock a cell reference as such: $A$1 - but when I attempt to lock a label, for example ‘total’, Libreoffice says there are no references in the cell.

Reproduction

1. Open a new sheet in Libreoffice Calc
2. Insert the text “total” (without quotes) in cell A1
3. Insert the value “2” (without quotes) in cell B1
4. Sheet → named Ranges and Expressions → Labels…
5. Introduce in the first bar: A1
6. Select “Contains row labels”
7. Introduce in the second bar: B1
8. Click Add
9. Insert the value “=total” (without quotes) in cell C1
10. Select cell C1
11. Press F4 or Sheet → Cycle Cell Reference Types

Error

No cell references are found in the selected cells.

Question

How do I lock the ‘total’ reference, so that I may pull down the handle from a cell that contains a formula that uses the ‘total’ reference, without making the second cell below the one that was pulled grab the cell below ‘total’?

Example File

Untitled 1.ods

[keme]

References to named ranges are always “absolute”, not “relative”. In other words, it is already locked.

Do you have a use case where you actually tried to pull the fill handle, and a reference by name behaves like a relative reference? If so, please post details (preferably with a sample file) by editing your question above. I am not able to force the issue which you have implied, not by any copy/paste method nor by using “fill”.

[davidmsousa]

Updated as per suggested.

[keme]

Yes, I missed that one. I never used the “auto label” feature so I forgot that it existed. Sorry!

I would use VLOOKUP() or related functions for this kind of reference in most cases, but there are use cases where that may be a bad choice.

If you name the range explicitly, it will behave like I describe above, as an absolute reference. (Your original formula will have the dynamic, “label based” behavior, but once you edit it or enter a new reference to the same named cell that new formula will be “absolute”.) The behavior will change slightly in other respects also.

If those are not viable workarounds, please describe the actual use case here: What is your data layout? Why do you need to “fill”? Are you the only user? Any other info you my think useful.

[keme]

To name a single cell, just select it and type the desired name in the “address bar” (leftmost item on the same toolbar where you find the formula entry bar). Multiple cells in a table can be named from labels in one go, using menu item Sheet - Named ranges ... - Define (I think that is the menu path in English. Can’t check right now).

[erAck]

References to named ranges are always “absolute”, not “relative”.

That is not true. It depends on whether absolute or relative references are used in the named expression.

Define a name MyLeft on B1 and use a reference A1 then in B1 =MyLeft references A1 and in B2 it references A2 and in C3 it references B3. If the reference in the named expression instead is $A$1 then all uses of =MyLeft reference the cell A1. Similar for A$1 the column is relative and the row is absolute and $A1 vice versa.

[alfyj72]

I have a related problem that occurs when I add a column to a worksheet causing the named range reference to move with the original data it refered to. For example, when the name Tb1S is defined as $H$11, and then I insert a new column to the left of column H, TB1S now points to $I$11. I would like to find a syntax that would be invariant on inserting rows, columns or cells. Otherwise, I would have to change a whole bunch (41) of named to use the INDIRECT(“H11”,1). I originally created the 41 named ranges using the create from left column option to create them all at once.

[erAck]

Of course the reference gets adjusted because $H$11 uses absolute references to point to a specific cell. To always make the reference point to a cell relative to the position where the name is used define the name using relative references, i.e. H11 (both column and row relative in this case).