how prevent data range from changing? [closed]

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?

edit retag reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-08-21 00:31:28.910790

Sort by » oldest newest most voted

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)

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:

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

more

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") ?

( 2015-05-06 14:12:22 +0100 )edit

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.

( 2015-05-07 00:16:10 +0100 )edit

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

( 2015-05-07 00:43:59 +0100 )edit

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?

more