Calc - why do an absolute reference change with target cell?

[teemo1]

In Libre Calc I have =$A$1 in B1
Why do the reference change if I move the content of A1 ?
If I drag A1 to A2, the content of B1 change to =$A$2. How do I “lock” the content of B1?

[erAck]

You can’t “lock” a direct reference. You can use a named range of a relative reference, e.g. on cell B1 define a named range rangename and as reference set A1 and in the B1 formula cell use =rangename then that formula points always one cell to the left. You could also use =INDIRECT("A1") with the downside that such is always reinterpreted on each change; on the other hand you could even move the formula cell around and it would always evaluate the A1 value, which it doesn’t with the example of the named range of relative reference (as that always points one to the left…).
YMMV.

[teemo1]

Aah, thank you very much. I marked it as a solution, but I’m still not quite done
=INDIRECT(“A1”) does exactly what I want. B1 will never be moved, but A1 might.
Next problem is to copy B1 down to get
=INDIRECT(“A2”)
=INDIRECT(“A3”)
=INDIRECT(“A4”)
etc.
Any easy solution to that (300 or more rows)?

[mikekaganski]

Use INDIRECT’s second argument to specify an R1C1-style reference:

=INDIRECT("RC[-1]";0)

[teemo1]

Thank you, that completes the formula:

=SUM($E$2:INDIRECT("RC[-4]",0))-SUM($F$2:INDIRECT("RC[-3]",0))

I can now move around with col E and F without breaking links from col I.

[erAck: edited to codify using ``` ]