Lose relative connection in formula on sort


J

JICDB

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$3000)

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.
 
Ad

Advertisements

E

Eduardo

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

if this helps please click yes thanks
 
Ad

Advertisements

J

JICDB

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top