Sort--Formulas don't adjust

[Earl]

I have a 120 line spreadsheet with some complex formulas
(IF statements and lookup tables). The lookup tables are
in a separate worksheet in the same file. When I sort the
spreadsheet by selecting the entire rows and sorting on
one column alphabetically, the columns with the formulae
do not adjust. For example, the formulas on each line
refer to the cell in column E on each row, ie Row 118
refers to the number in cell E118. When sorted, this row
becomes row 12 but the formula still refers to cell E118.
There are no $ in use for the E118 reference. When I
build a similar spreadsheet but smaller the references
adjust when sorted. Why don't they adjust when the
spreadsheet is larger?

 

[Frank Kabel]

Hi
this is the normal behaviour (I don't think that this has worked for
your smaller sheet). Formulas are attached to cells and not to the
values within these cells. So a sorting would destroy your linked
values

 

[Earl]

I found that when I remove the reference to the worksheet,
ie change Vacation!E118 to just E118 the formulas adjust
the way they should. This seems to be a bug in Excel
unless I'm doing something wrong. Any ideas?

 

[Earl]

No, its not normal behaviour. If you have a spreadsheet:
A B C D
Mary 25 10 =B1*C1=250
John 55 20 =B2*C2=1100
And you sort alphabetically on column A, you should not
get:
John 55 20 =B1*C2=550
Mary 25 10 =B2*C1=500
And that is exactly what is happening in my spreadsheet.
All references adjust except for the one called Vacation!
E### where Vacation is the name of the sheet and the
numbers that don't change are in column E. I'm at a loss
to explain this which looks like a bug.