Formula Integrity Not Preserved During Sort in Excel 2000

Greetings! I've used Excel for years, but this one caught me off guard. In
Excel 2000, I have the following data:

A B
1 3 1
2 1 10
3 2 100
4 5 1000
5 4 =B1+B2+B3

When I sort all five rows by column A, my formula gives me a #REF! error and
the formula is changed to "=#REF!+B1+B2". Doesn't Excel track the relative
changes of formulas during a sort? It seems to have tracked the 10 and the
100 in the table above but somehow lost track of where the 1 went.

This came to my attention when a formula returned an invalid value after a
sort -- even worse, I'd rather have an error than bad data!

Can anyone shed some light on this and is there a workaround? I tried
absolute references, but that didn't work.

Thanks in advance!
--
Kevin

Dear Kevin:

The problem here is that for example the cell B2; it's formula is =B1; but
when you sort it, it would go one row up, so the formula would be "B0?".
That's what the #REF error is. You can't use Absolute such as =B$1, because
if you move that formula up, it will still be B$1.

The only solution I can think of is to copy the calculated column (B1:B5)
and special paste it as values only. That way if you sort it, it will sort on
the actual values, but you will loose your formulas.

That's my idea, but I don't know if in the context of what you are doing,
this is useful. Otherwise, the workaround would be to use a macro to sort
it... but you would have to write the code for sorting, that is you won't be
able to sort using the sort method for range.

:D

See ya!

"Kevin" wrote:

> Greetings! I've used Excel for years, but this one caught me off guard. In
> Excel 2000, I have the following data:
>
> A B
> 1 3 1
> 2 1 10
> 3 2 100
> 4 5 1000
> 5 4 =B1+B2+B3
>
> When I sort all five rows by column A, my formula gives me a #REF! error and
> the formula is changed to "=#REF!+B1+B2". Doesn't Excel track the relative
> changes of formulas during a sort? It seems to have tracked the 10 and the
> 100 in the table above but somehow lost track of where the 1 went.
>
> This came to my attention when a formula returned an invalid value after a
> sort -- even worse, I'd rather have an error than bad data!
>
> Can anyone shed some light on this and is there a workaround? I tried
> absolute references, but that didn't work.
>
> Thanks in advance!
> --
> Kevin