[libreoffice-users] Re: Fixed range that does not change

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.



--
E-mail to users+help@libreoffice.org for instructions on how to unsubscribe
List archives are available at http://www.libreoffice.org/lists/users/
All messages you send to this list will be publicly archived and cannot be deleted