Dynamic vlidation drop down list

[Flashboy]

Hi Guys

Dose anyone know if its possible to have a dynamic drop down list? I need to build a report for 50+ teams results and been asked if its possible to have the dropdown ordered worst 10 performers each week. would the list need to be changed in the validation each week of can it change dynamically with me changing it each week?

Thanks

Flash

 

[oldbrewer]

if you have your performance list, it is easy to make another list of the 10 worst performers and use that for the drop down. Each week the worst performers change, so does the drop
down automatically

beware though if 40 teams score 100% and 10 teams score 99.99% you will label those 10 as worst performers...

also beware teams can only perform as well as "management" lets them so drops in performance are likely to be the fault of manageemnt

study the work of Deming - it is accepted that 80% of problems are down to management.........

 

[Chrisdontm]

I know I saw a dynamic drop down on you tube, I think it took about 10 steps to do it...

 

[mick0005]

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​