How prevent data range from changing?

[LibreGuy]

Hi guys,

I’m experiencing a problem with Calc.

I selected the first row of a spreadsheet and defined it as a data range. After creating the range and checking it, I see the range got defined perfectly, like $A$1:$AMJ$1.

Now the problem is, when I select a column and delete this entire column, my defined range changes to $A$1:$AMI$1. The difference is at the end: AMJ changes to AMI. This means the last column, column AMJ, is no longer part of the defined range. How can I stop this behavior? I don’t want the defined range to change … ever. Is that somehow possible?

[mariosv]

Define the range name with a function:

OFFSET($Sheet1.$A$1;0;0;1;1023)
(edited), really need to be absolute reference for use in other cells different than A1, but row 1 or column A, can not be deleted or inserted.
If you need to insert in row 1 or column A, then with a more bit complicate formula can be done.
OFFSET(INDIRECT("$Sheet1.$A$1");0;0;1;1023)
OFFSET(INDIRECT(ADDRESS(1;1;1;1;“Sheet1”));0;0;1;1023)

or hard coded:

INDIRECT(“Sheet1.A1:AMJ1”)

Sheet name can be avoided if formula are going to be used in the same sheet where the range is.

Edited 20150506
sorry there was an error in OFFSET formula.

To use the formulas with a name:

Menu/Insert/Names/Manage [Ctrl+F3]

Select Add, enter the name for the formula and in the range enter the formula (without =)

Also you can use the functions inside other formulas.

Attached a sample file.

PreserveRanges.ods

[LibreGuy]

Thanks, but where exactly do I “Define the range name with a function”? Where do I paste the function? Do I need to do something like MyRange=INDIRECT(“Sheet1.A1:AMJ1”) ?

[Lupp]

Simply enter the expression into the line labelled ‘Range:’ of the ‘Names’ dialogue. Generally the naming tool can be used to create (and manage) named expressions. Constant (range-)references are only a simple example. Not only OFFSET but, in principle, any function may occur.

[LibreGuy]

Thank you both. I used the INDIRECT function and gave it a name. Seems to be a good solution.

[LibreGuy]

Although the given answer works fine, it seems to slow down everything. I think the INDIRECT function is doing this. Is there perhaps an alternative function I could use that does not slow things down?