Re: Sorting column of formulae destroys them.

[Leonard Mada <discoleo gmx net>]

Hello everyone,

Hal Ashburner wrote:
> Peap wrote:
>   
> > [...]
> > When I sort A22-A401, the vast majority of the cells display "#REF!". 
> > Within the cells, a seemingly random selection of first and second cell refs
> > became "#REF!".
> >
> > [...]  
> >     
> you probably want to do something like copying A1:A401, edit->paste 
> special->as values to another column, let's say col B.
> Then you can sort column B.
> You can't really sort formulas that have to be re-evaluated, only values.
>   

This is a rather tricky question. There should be a possibility to 
preserve the formulas after a sort-operation, and, IF I am correct this 
is also possible:

- you have to rewrite the formulas as =$A$1 + $A$2, ...
- this should preserve the formula

Please note, this makes only sense IF you sort only the cells with the 
formulas (as you said A22-A401). It will yield nonsense IF you sorted 
A1-A401. [I actually did NOT test it on your specific example - but it 
should behave as anticipated.]

Sincerely,

Leonard