Problems with columns "sorting"

[mpanty]

I manage a sports league, and use Excel to do team rankings with points,
games played, wins, ties, etc.

While trying to sort the list in ascending and descending order for
points, it works fine, but if I do the same thing for the other columns
(e.g. Goals scored), Excel just starts ranking them completely
randomly!! I don't understand....

Oh I should mention that all the values in each column result from
functions (namely I have another worksheet with team scores (in another
tab), and have set up functions that allow me to count the number of
wins, goals scored etc.). I don't know if that's a factor... apparently
the fact that the functions refer to another tab might be a problem...?

Can someone help?

 

[mpanty]

I seem to have found the problem... when I click on the sort button, the
references in the functions I use in each cell also change (that is if
cell E7 had a function referring to cell A7, after the sorting the
reference will be to a completely different cell in column A (e.g.
A12), decided randomly (?).

I don't know if I've explained myself clearly, but *is there a way to
prevent this*? (other than putting a $ sign before the column... If I
do that, I can't copy the formula to all other rows, and I'd have to
edit each cell one by one to add the $).

I don't see why Excel decides to rearrange the cell references...

 

[Dave Peterson]

I try to not have any formulas that point directly to a cell on another sheet.

What I do is to try to find a key value that I can use to match up on the other
sheet. I type in that key value and use =vlookup() or =index(match()) to return
the info I need.

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

 

[mpanty]

Hi Dave. That would work if my functions were simply returning *values
*from the other sheet, but they are actually performing
*calculations*.

Namely to find out the number of games a team has played, I use the
COUNTIF function, to find out their number of wins, the SUMPRODUCT
function, and the number of goals, the SUMIF function etc. I can't
replace them with VLOOKUP and INDEXMATCH, even if I wanted to...

The temporary solution I'm using right now is to copy just the values
the rankings table gives me, to another table right below, and thus
with a table of only values ranking is very easy. But it's kind of
cumbersome...

 

[Dave Peterson]

can't you use =countif() and set the criteria to that key value on the same row?

 

[mpanty]

I already use COUNTIF, but only for the number of games played (i.e. the
number of times the team name appears in my list).

For the number of wins, since each team might play away or home, I have
to set up the SUMPRODUCT function such that it matches the team name in
the "home team" column with a "W" in its respective column, then the
"away team" column and the "W" in another adjacent column. It probably
sounds complicated, but it works.

Then for the number of goals scored and conceded, I use SUMIF (again,
because the team name might appear in the "home team" columnn or the
"away team" column.

 

[Dave Peterson]

I don't have another suggestion.

Sorry.

I already use COUNTIF, but only for the number of games played (i.e. the
number of times the team name appears in my list).

For the number of wins, since each team might play away or home, I have
to set up the SUMPRODUCT function such that it matches the team name in
the "home team" column with a "W" in its respective column, then the
"away team" column and the "W" in another adjacent column. It probably
sounds complicated, but it works.

Then for the number of goals scored and conceded, I use SUMIF (again,
because the team name might appear in the "home team" columnn or the
"away team" column.