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