Fixed range that does not change

users
#1

I want to set up a formula to operate on a fixed range of cells that
never changes even if I insert or delete rows or columns. If I say
$A$1:$A$100, and then I delete 10 rows, the range in the formula
changes to $A$1:$A$90. I don't want it to change. Is there a way to
"carve it in stone"? The actual range is $A$1:$A$1048576, because I
can't just do $A:$A like I can in Excel, and I have a macro that
deletes the content and imports a new set of rows from a text file.
Every time I run the macro to refresh the data, the range shrinks, and
in a few months time it will be down to $A$1:$A$1, or gone entirely. I
don't want to delete just the contents of the cells, I want Ctrl-End
to go to the last row of actual data rather than the last row that has
ever had data in it. Any ideas? Named ranges are no different, they
shrink as rows are deleted as well.

Phil Hibbs.

#2

Hi,

-----Message d'origine-----

#3

Ah, thanks! It's a bit long-winded but does the job.

Phil.

#4

Hm, can't create a Named Range like that, so I can't use it for a
filter. Damn. Why oh why doesn't OOo support A:A?

Phil.

#5

Ah. I think I might have found a bug.

1. Put 'a' in A1 and 'z' in A2
2. Put 'a' in C1 and 1 in D1
3. Put 'z' in C1048576 and 26 in D1048576
4. Put formula in B1 =VLOOKUP(A1,INDIRECT("C1:D1048576"),2,0)
5. Copy formula into B2, which should give
=VLOOKUP(B1,INDIRECT("C1:D1048576"),2,0)

Everything works fine, the first vlookup returns 1 and the second
returns 26... then...

6. Delete Row 3

The vlookup in B2 now returns blank! Can anyone else replicate this
behaviour and explain why?

7. Put 'z' in C2, then remove it

The vlookup in B2 works again!

Phil Hibbs.

#6

Phil:

I get the same result using OOo 3.2.1 on Vista Sp2. (Substituting 65536 as the last row)
Delete a row the result is blank. (MRI Inspection Tool shows a value of 0.0)
Enter 'z' in C2 and it returns 26 again.
And
Insert a row and it returns 26 again.

TomW

#7

Reported. Thanks.
https://bugs.freedesktop.org/show_bug.cgi?id=31577

Phil Hibbs.

#8

:

Phil Hibbs.
--
Don't you just hate self-referential sigs?

Such as . . . what?

:slight_smile:

#9

Am 11.11.2010 10:53, Phil Hibbs wrote:

I want to set up a formula to operate on a fixed range of cells that
never changes even if I insert or delete rows or columns. If I say
$A$1:$A$100, and then I delete 10 rows, the range in the formula
changes to $A$1:$A$90. I don't want it to change. Is there a way to
"carve it in stone"? The actual range is $A$1:$A$1048576, because I
can't just do $A:$A like I can in Excel, and I have a macro that
deletes the content and imports a new set of rows from a text file.
Every time I run the macro to refresh the data, the range shrinks, and
in a few months time it will be down to $A$1:$A$1, or gone entirely. I
don't want to delete just the contents of the cells, I want Ctrl-End
to go to the last row of actual data rather than the last row that has
ever had data in it. Any ideas? Named ranges are no different, they
shrink as rows are deleted as well.

Phil Hibbs.

OFFSET($A$1;0;0;100;1) does the job unless you delete row #1.

#10

Like! (Well, once I realised that the last 1 was the total number of
columns, not an offset, so I needed 2 to get 2 columns, and 1048576
and not 1048575 to get all the rows...)

Phil.