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 wrote:
>
> 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...
>
>
> --
> mpanty
> ------------------------------------------------------------------------
> mpanty's Profile: http://www.excelforum.com/member.php...o&userid=21377
> View this thread: http://www.excelforum.com/showthread...hreadid=503224

--

Dave Peterson

[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 wrote:
>
> 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...
>
> --
> mpanty
> ------------------------------------------------------------------------
> mpanty's Profile: http://www.excelforum.com/member.php...o&userid=21377
> View this thread: http://www.excelforum.com/showthread...hreadid=503224

--

Dave Peterson

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

mpanty wrote:
>
> 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.
>
> --
> mpanty
> ------------------------------------------------------------------------
> mpanty's Profile: http://www.excelforum.com/member.php...o&userid=21377
> View this thread: http://www.excelforum.com/showthread...hreadid=503224

--

Dave Peterson