MS Excel and 'league' tables
Discussion
Hello Al
I have another table which collates all the information, (in this case AMOUNT OF SALES, WARRANTY AND FINANCE) i want to use that table to automatically produce the results on another table. (i know its a bit dificult to explain on here
)
The method allow me to not use the 'sort by' function.
The silly thing is have done it before, but is was some time ago and i have a memory like a seive
I have another table which collates all the information, (in this case AMOUNT OF SALES, WARRANTY AND FINANCE) i want to use that table to automatically produce the results on another table. (i know its a bit dificult to explain on here
) The method allow me to not use the 'sort by' function.
The silly thing is have done it before, but is was some time ago and i have a memory like a seive

Set up the column headers e.g. "Name"; "Sales this Month"; "Sales Cumulative" etc. Enter the data for each Name. Select all the rows including your Header row (not just the column that you want to sort). Go to "DATA" on the menu, select "SORT", use drop down menu to select the column with the data you want to sort, use the radio button to chose descending so the salesperson with the highest sales is at the top, use the radio button to select "Header Row" - press enter, SORTED! Pour yourself a beer and then think about ways you can add targets and performance measures!
Email me if you need help,
Hutch
Email me if you need help,
Hutch
If my reading is right you want the spreadsheet to autosort, so that every week/month whatever you input the sales info, and then it sorts them automatically?
My guess is that you need to use a Macro, but iots so long since I used macros I can't remember where to start!
Might have a look later if you haven't got an answer.
My guess is that you need to use a Macro, but iots so long since I used macros I can't remember where to start!
Might have a look later if you haven't got an answer.
You guys are excellent!
Big Al/Hut49 is was you two who jogged my memory, its all sorted now
One last question. what happens if there are joint scores, i.e if two salesman both have e.g. 20 cars sold? How can i show joint scores e.g Equal 2nd?
ChrisGR31 I forgot about the macros so thank you aswell for reminding me
Ahhhhh the power of PH
I feel a bonus (not a boner) coming on!
Regards
Umar
>> Edited by UMAR B on Saturday 19th April 12:52
Big Al/Hut49 is was you two who jogged my memory, its all sorted now
One last question. what happens if there are joint scores, i.e if two salesman both have e.g. 20 cars sold? How can i show joint scores e.g Equal 2nd?
ChrisGR31 I forgot about the macros so thank you aswell for reminding me
Ahhhhh the power of PH
I feel a bonus (not a boner) coming on!
Regards
Umar
>> Edited by UMAR B on Saturday 19th April 12:52
Use the RANK function:
Enter the formula: =RANK(number,ref,order) into a cell in the row for salesperson 1.
In the formula above:
'number' is the cell ID (eg B2) for the value that you want to rank the salespersons on (e.g. 20 cars sold in your example)
'ref' is the range of cell IDs that you want to include in the ranking exercise and is expressed eg B2:B10
'order' is how you want to organise the ranking ie use a "0" if you want to rank lowest to highest and a non-zero number if you want to rank highest down to lowest.
So the entry for the cell in row 2 might look like
=RANK(B2,B2:B10,1)
Then copy this cell and paste it down the column for each salesperson.
This would give you each salespersons rank and if there were equal scores for one or more salespersons then they would get the same ranking and the ranking would begin after them with the appropriate gap in numbering.
If you can't make it work - email me and I will email you a mini example.
Good luck
Hutch
Enter the formula: =RANK(number,ref,order) into a cell in the row for salesperson 1.
In the formula above:
'number' is the cell ID (eg B2) for the value that you want to rank the salespersons on (e.g. 20 cars sold in your example)
'ref' is the range of cell IDs that you want to include in the ranking exercise and is expressed eg B2:B10
'order' is how you want to organise the ranking ie use a "0" if you want to rank lowest to highest and a non-zero number if you want to rank highest down to lowest.
So the entry for the cell in row 2 might look like
=RANK(B2,B2:B10,1)
Then copy this cell and paste it down the column for each salesperson.
This would give you each salespersons rank and if there were equal scores for one or more salespersons then they would get the same ranking and the ranking would begin after them with the appropriate gap in numbering.
If you can't make it work - email me and I will email you a mini example.
Good luck
Hutch
Gassing Station | General Gassing [Archive] | Top of Page | What's New | My Stuff


