Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with columns "sorting"
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: http://www.excelforum.com/member.php...o&userid=21377 View this thread: http://www.excelforum.com/showthread...hreadid=503224 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with columns "sorting"
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with columns "sorting"
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with columns "sorting"
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with columns "sorting"
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with columns "sorting"
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with columns "sorting"
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to combine several columns into a single column | Excel Discussion (Misc queries) | |||
Hiding columns and custom views problem | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Excel - columns into rows | Excel Discussion (Misc queries) | |||
Excel - Columns into rows | New Users to Excel |