Hi,
newbie to the board, please can somebody help me with this query?
Excel 2003 or 2007
I am creating a workbook with two sheets, data entry sheet and scoreboard sheet for a golf tournament. The data sheet contains two columns, the player name and a numerical value for their score. I then want to create a pivot table do display the scores in ascending order, (this is the leaderboard) as I need to preserve the original data sheet in its existing format. This is simple enough and I've has no problem with this. The difficulty I have is that if two or more competitors have the same score (e.g 72) then I need to be able to assign another numerical value to their score, effectively ranking that subset of the data. I thought this would be simple, just create a new column, add the extra value to the pivot and sort the data by the "score" value and then the "rank" value. It seems I am not able to do this in the pivot. As I can only sort on one value. I then had the brainwave of creating a new field and concatenating the score field and the rank field to arrive at a new value. I added this to my pivot as the data but when I try to display it as a SUM, in the pivot, I am presented with the value "1" instead of the value in the source cell. I am unable to sort on this value either. no error message, just nothing happens.
I hope the above makes sense, I've put a small example below, the set of data is how it may be entered in the data sheet. In my pivot, I'd like to sort by the concatenated field. I have tried to set this field as a numerical value but that doesn't help either.
Name Score Rank Concatenated field
player1 72 3 713
player1 72 4 714
player1 72 1 711
player1 72 2 712
If there is another way to acheive my aim, then I'm open to suggestions.
Thanks in advance
Nick
newbie to the board, please can somebody help me with this query?
Excel 2003 or 2007
I am creating a workbook with two sheets, data entry sheet and scoreboard sheet for a golf tournament. The data sheet contains two columns, the player name and a numerical value for their score. I then want to create a pivot table do display the scores in ascending order, (this is the leaderboard) as I need to preserve the original data sheet in its existing format. This is simple enough and I've has no problem with this. The difficulty I have is that if two or more competitors have the same score (e.g 72) then I need to be able to assign another numerical value to their score, effectively ranking that subset of the data. I thought this would be simple, just create a new column, add the extra value to the pivot and sort the data by the "score" value and then the "rank" value. It seems I am not able to do this in the pivot. As I can only sort on one value. I then had the brainwave of creating a new field and concatenating the score field and the rank field to arrive at a new value. I added this to my pivot as the data but when I try to display it as a SUM, in the pivot, I am presented with the value "1" instead of the value in the source cell. I am unable to sort on this value either. no error message, just nothing happens.
I hope the above makes sense, I've put a small example below, the set of data is how it may be entered in the data sheet. In my pivot, I'd like to sort by the concatenated field. I have tried to set this field as a numerical value but that doesn't help either.
Name Score Rank Concatenated field
player1 72 3 713
player1 72 4 714
player1 72 1 711
player1 72 2 712
If there is another way to acheive my aim, then I'm open to suggestions.
Thanks in advance
Nick
