Ask Your Question
0

how prevent data range from changing?

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 close merge delete

2 Answers

Sort by » oldest newest most voted
0

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:

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

edit flag offensive delete link more

Comments

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
0

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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 475 times

Last updated: May 07 '15