getting accurate results when sorting

[LineRicher]

I have a created a workbook to keep stats on a small dart league. For some
reason I'm have problems when I sort. Its like the information isn't locked
in. The formulas are assigned to cells, but when I perform a sort, its not
assigned to the right person! Not sure how to explain it properly, but if
someone has any clue what I'm talking about, can you email me at
(e-mail address removed)

Please and thanks

 

[Gary Brown]

Sounds like your sort range isn't including all the columns so some columns
stay put and others move.
Ouch!
Make sure you have all the columns in your database in your sort range.
HTH,
Gary Brown

 

[Line Richer]

Hi Gary,
the sort range isn't a problem, the problem seems to occur because of the
formulas when I sort.

For example: I have 13 worksheets so far (one for every week of the season,
RegularSeason W1,RegularSeason W2, etc...) then I have one called
OverallTotals (which is the cumulative of all the weeks) the formula for the
column called "Total score" for example is:
=SUM('Regular Season W1'!E3,'Regular Season W2'!E3,'Regular Season
W3'!E3,'Regular Season W4'!E3,'Regular Season W5'!E3,'Regular Season
W6'!E3,'Regular Season W7'!E3,'Regular Season W8'!E3,'Regular Season
W9'!E3,'Regular Season W10'!E3,'Regular Season W11'!E3,'Regular Season
W12'!E3,'Regular Season W13'!E3).

When I perform a SORT by any column, the numbers don't follow the
appropriate rows (name of the players).

Any idea?

I've been using Excel for years to do a lot more complexe spreadsheets than
this, and this is the first time I encounter a problem of this nature!

 

[Ragdyer]

First of all, you can shorten your Sum formula to:

=SUM('Regular Season W1:Regular Season W13'!E3)
*IF*
W1 is the leftmost and W13 is the rightmost sheet of all the sheets to be
totaled, and, *all* the others are in between.

Next,
When you say "Sort", are you really looking to perhaps place the highest, or
lowest score at the top of a list, with the name of the player beside it?

This type of "ranking" can be accomplished *automatically* using formulas,
so that manual sorting would be *unnecessary*.

Say your summary sheet has the players listed in Column A, from A1 to A15.
Say the totals formula:
=SUM('Regular Season W1:Regular Season W13'!E3)
is in Column D, copied down from D1 to D15.

You could enter this formula in say F1:

=LARGE($D$1:$D$15,ROW(A1))

And enter this *array* formula in G1:

=INDEX($A$1:$A$15,SMALL(IF($D$1:$D$15=F1,ROW($A$1:$A$15)),COUNTIF(F1:$F$15,F
1)))
Array formulas are entered using <Ctrl> <Shift> <Enter>, instead of just the
regular <Enter>.
If done correctly, XL will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Now, select *both* F1 & G1, and drag down to copy to Row15.

This will give you the scores listed with the highest first, and the name of
the player listed along side the score.
Ties will be displayed in the reverse order that they're listed in the main
list.
This can be changed if you wish, to display ties in the same order that
they're entered in the main list by simply changing "SMALL" in the formula
in G1 to "LARGE".