Lose relative connection in formula on sort
 

This has happened to me a few times and it is so frustrating this time I
would like to find an answer instead of manually fixing it. I have a formula
which uses sumif to look at other worksheets to sum the ridership of a
particular bus route. (the formula is below). The route number is in column
A. When I sort all of the columns including the columns with sumif formulas
(Mon-Sun below) the formula stays connected to the original route number in
column A - doesn't move to the new route that now populates that row.
Example

=SUMIF(Wk1!$A$2:$A$3000,Summary!A5,Wk1!$I$2:$I$300 0)

ROUTE Mon Tue Wed Thur Fri Sat Sun
208 1 2 3 4 5 6 7
241 8 9 10 11 12 13 14
209 15 16 17 18 19 20 21

Route 208 is in row 5. When I sort this the route numbers move in
numberical order (using Data-sort) but the reference to the route number in
column A doesn't move with the sort.

ROUTE Mon Tue Wed Thur Fri Sat Sun
208 1 2 3 4 5 6 7
209 8 9 10 11 12 13 14
241 15 16 17 18 19 20 21


Is this some fluke in Excel or am I doing something fundamentally wrong.

Lose relative connection in formula on sort
 

Hi,
You need to select the whole range and then sort by column A

if this helps please click yes thanks

"JICDB" wrote:

This has happened to me a few times and it is so frustrating this time I
would like to find an answer instead of manually fixing it. I have a formula
which uses sumif to look at other worksheets to sum the ridership of a
particular bus route. (the formula is below). The route number is in column
A. When I sort all of the columns including the columns with sumif formulas
(Mon-Sun below) the formula stays connected to the original route number in
column A - doesn't move to the new route that now populates that row.
Example

=SUMIF(Wk1!$A$2:$A$3000,Summary!A5,Wk1!$I$2:$I$300 0)

ROUTE Mon Tue Wed Thur Fri Sat Sun
208 1 2 3 4 5 6 7
241 8 9 10 11 12 13 14
209 15 16 17 18 19 20 21

Route 208 is in row 5. When I sort this the route numbers move in
numberical order (using Data-sort) but the reference to the route number in
column A doesn't move with the sort.

ROUTE Mon Tue Wed Thur Fri Sat Sun
208 1 2 3 4 5 6 7
209 8 9 10 11 12 13 14
241 15 16 17 18 19 20 21


Is this some fluke in Excel or am I doing something fundamentally wrong.

Lose relative connection in formula on sort
 

I did select the entire range, with and without headers (trying to see if it
made a difference).

"Eduardo" wrote:

Hi,
You need to select the whole range and then sort by column A

if this helps please click yes thanks

"JICDB" wrote:

This has happened to me a few times and it is so frustrating this time I
would like to find an answer instead of manually fixing it. I have a formula
which uses sumif to look at other worksheets to sum the ridership of a
particular bus route. (the formula is below). The route number is in column
A. When I sort all of the columns including the columns with sumif formulas
(Mon-Sun below) the formula stays connected to the original route number in
column A - doesn't move to the new route that now populates that row.
Example

=SUMIF(Wk1!$A$2:$A$3000,Summary!A5,Wk1!$I$2:$I$300 0)

ROUTE Mon Tue Wed Thur Fri Sat Sun
208 1 2 3 4 5 6 7
241 8 9 10 11 12 13 14
209 15 16 17 18 19 20 21

Route 208 is in row 5. When I sort this the route numbers move in
numberical order (using Data-sort) but the reference to the route number in
column A doesn't move with the sort.

ROUTE Mon Tue Wed Thur Fri Sat Sun
208 1 2 3 4 5 6 7
209 8 9 10 11 12 13 14
241 15 16 17 18 19 20 21


Is this some fluke in Excel or am I doing something fundamentally wrong.