I have formulae entered i cols A3 to AX3. I select the range name then drag down from cell AX3 as far as is needed. In cell AA3 is a number. I do not want this number to be incremented; it is a Id index number so must remain the same in all rows.
I have set the format of the cell to Text but when dragging the cells increment!
What am I doing wrong?
Any advice please?
Try this way:
Name box enter A3:AX100 (100 - the last row) and press Enter..uno:FillDown).Hold the Ctrl key during the drag&drop operation.
that doesnt work with more than one cell in a row is dragged … with me LO6.1.5
@athegn : drag A3:Z3 … drag (with <ctrl>) AA3 … drag AB3:AX3
Error by $USER I’m hitting <ctrl> before mousebutton
Works for me with LO 7.3.
t79701.ods (13.2 KB)
contains a number some text with numeric suffix and an absolute reference $A$1. When I select A3:E3 and Ctrl+Drag&Down I get identical row copies.
I have changed the cell formula to =$AA$2; this of course works.
How ever I did try something else first.
I range named cell AA2 as My_Contact_Id. I then put =$My_Contact_Id in cell AA3. When I pressed ENTER the cell showed =$my_contact_id; this shows a #NAME error. I tried renaming the range name but Libre would not let me capitalise the M C or I.
Why
This looks completely different to the initial description:
The original description talks about “incrementing number in a cell”, this one talks about “changing” reference. The reference like A1 is a relative reference; you need to learn considering them not as “cell A1”, but as “cell two rows above, one column to the left” (i.e., it depends on which cell you are in when enter it). When you think of it this way, you will see that when you copy a formula like =cell at two rows above, one column to the left, the absolutely identical formula in the other cell will look a bit differently (like =A2 when you copy the formula down).
This is a user error. Named range does not need, and does not allow you to use “relative/absolute” addressing with it. So: only =My_Contact_Id, not =$My_Contact_Id.