Chris, I'd be happy to help but I may need to know more requirements. I would think this would be pretty simple...
Depending on how your data is formatted, you may be able to just use a pivot table. You set up the pivot table where you have the names of the people as the row value, and the performance metric as their data value (Sum of Metric). Add a report filter of the "week" so you can select which week the data is being used by. Once you have set this up, simply right click on one of the employee's performance metric scores and choose to "Show Values As" and then you can choose to "Rank Values Largest to Smallest" or "Smallest to Largest" (depending on your needs... I don't know for your peformance what is better - a low number or high number.) It will assign a number (1-50) ranking them (with 1 being the best). Sort the pivot table in ascending order by performance ranking and then your top 10 performers will be at the top of the pivot table.
Then, just point your data validation source range as the first 10 records of the pivot table. When you select a new week, the top 10 performers will always be the top 10 records of the pivot table.
Are there other requirements?
It would look like this and your data validation range would be fixed. Cells F4:F13... assuming you can get your data into a pivot table friendly format (Tabular data):
Excel 2010
| A | B | C | D | E | F | G |
---|
1 | Week | Country | Score | | | Week | Week1 |
2 | Week1 | China | 100 | | | | |
3 | Week1 | Hong Kong | 95 | | | Row Labels | Sum of Score |
4 | Week1 | India | 90 | | | China | 1 |
5 | Week1 | Indonesia | 85 | | | Hong Kong | 2 |
6 | Week1 | Japan | 80 | | | India | 3 |
7 | Week1 | Korea | 75 | | | Indonesia | 4 |
8 | Week1 | Malaysia | 70 | | | Japan | 5 |
9 | Week1 | Philippines | 65 | | | Korea | 6 |
10 | Week1 | Singapore | 60 | | | Malaysia | 7 |
11 | Week1 | Taiwan | 55 | | | Philippines | 8 |
12 | Week1 | Thailand | 50 | | | Singapore | 9 |
13 | Week1 | Vietnam | 45 | | | Taiwan | 10 |
14 | Week2 | China | 45 | | | Thailand | 11 |
15 | Week2 | Hong Kong | 50 | | | Vietnam | 12 |
16 | Week2 | India | 55 | | | Grand Total | |
17 | Week2 | Indonesia | 60 | | | | |
18 | Week2 | Japan | 65 | | | | |
19 | Week2 | Korea | 70 | | | | |
20 | Week2 | Malaysia | 75 | | | | |
21 | Week2 | Philippines | 80 | | | | |
22 | Week2 | Singapore | 85 | | | | |
23 | Week2 | Taiwan | 90 | | | | |
24 | Week2 | Thailand | 95 | | | | |
25 | Week2 | Vietnam | 100 | | | | |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1