Ask Your Question

how prevent data range from changing? [closed]

asked 2015-05-06 00:56:48 +0200

LibreGuy gravatar image

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 flag offensive 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

2 Answers

Sort by » oldest newest most voted

answered 2015-05-06 03:08:55 +0200

m.a.riosv gravatar image

updated 2015-05-06 22:59:19 +0200

Define the range name with a function:

(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.

or hard coded:


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.


edit flag offensive delete link 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") ?

LibreGuy gravatar imageLibreGuy ( 2015-05-06 14:12:22 +0200 )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.

Lupp gravatar imageLupp ( 2015-05-07 00:16:10 +0200 )edit

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

LibreGuy gravatar imageLibreGuy ( 2015-05-07 00:43:59 +0200 )edit

answered 2015-05-07 01:57:47 +0200

LibreGuy gravatar image

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?

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2015-05-06 00:56:48 +0200

Seen: 832 times

Last updated: May 07 '15