software

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
------------------------------------------------------------------------
mpanty's Profile: www.excelforum.com/member.php...oamp;userid=21377
View this thread: www.excelforum.com/showthread...hreadid=503224
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: www.excelforum.com/member.php...oamp;userid=21377
View this thread: www.excelforum.com/showthread...hreadid=503224I 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:
www.contextures.com/xlFunctions02.html (for =vlookup())
and
www.contextures.com/xlFunctions03.html (for =index(match()))

mpanty wrote:
gt;
gt; I seem to have found the problem... when I click on the sort button, the
gt; references in the functions I use in each cell also change (that is if
gt; cell E7 had a function referring to cell A7, after the sorting the
gt; reference will be to a completely different cell in column A (e.g.
gt; A12), decided randomly (?).
gt;
gt; I don't know if I've explained myself clearly, but *is there a way to
gt; prevent this*? (other than putting a $ sign before the column... If I
gt; do that, I can't copy the formula to all other rows, and I'd have to
gt; edit each cell one by one to add the $).
gt;
gt; I don't see why Excel decides to rearrange the cell references...
gt;
gt;
gt; --
gt; mpanty
gt; ------------------------------------------------------------------------
gt; mpanty's Profile: www.excelforum.com/member.php...oamp;userid=21377
gt; View this thread: www.excelforum.com/showthread...hreadid=503224

--

Dave Peterson


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: www.excelforum.com/member.php...oamp;userid=21377
View this thread: www.excelforum.com/showthread...hreadid=503224can't you use =countif() and set the criteria to that key value on the same row?mpanty wrote:
gt;
gt; Hi Dave. That would work if my functions were simply returning *values
gt; *from the other sheet, but they are actually performing
gt; *calculations*.
gt;
gt; Namely to find out the number of games a team has played, I use the
gt; COUNTIF function, to find out their number of wins, the SUMPRODUCT
gt; function, and the number of goals, the SUMIF function etc. I can't
gt; replace them with VLOOKUP and INDEXMATCH, even if I wanted to...
gt;
gt; The temporary solution I'm using right now is to copy just the values
gt; the rankings table gives me, to another table right below, and thus
gt; with a table of only values ranking is very easy. But it's kind of
gt; cumbersome...
gt;
gt; --
gt; mpanty
gt; ------------------------------------------------------------------------
gt; mpanty's Profile: www.excelforum.com/member.php...oamp;userid=21377
gt; View this thread: www.excelforum.com/showthread...hreadid=503224

--

Dave Peterson


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 quot;home teamquot; column with a quot;Wquot; in its respective column, then the
quot;away teamquot; column and the quot;Wquot; 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 quot;home teamquot; columnn or the
quot;away teamquot; column.--
mpanty
------------------------------------------------------------------------
mpanty's Profile: www.excelforum.com/member.php...oamp;userid=21377
View this thread: www.excelforum.com/showthread...hreadid=503224I don't have another suggestion.

Sorry.

mpanty wrote:
gt;
gt; I already use COUNTIF, but only for the number of games played (i.e. the
gt; number of times the team name appears in my list).
gt;
gt; For the number of wins, since each team might play away or home, I have
gt; to set up the SUMPRODUCT function such that it matches the team name in
gt; the quot;home teamquot; column with a quot;Wquot; in its respective column, then the
gt; quot;away teamquot; column and the quot;Wquot; in another adjacent column. It probably
gt; sounds complicated, but it works.
gt;
gt; Then for the number of goals scored and conceded, I use SUMIF (again,
gt; because the team name might appear in the quot;home teamquot; columnn or the
gt; quot;away teamquot; column.
gt;
gt; --
gt; mpanty
gt; ------------------------------------------------------------------------
gt; mpanty's Profile: www.excelforum.com/member.php...oamp;userid=21377
gt; View this thread: www.excelforum.com/showthread...hreadid=503224

--

Dave Peterson