Dynamic vlidation drop down list

Flashboy

New Member
Joined
May 8, 2014
Messages
19
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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

Well-known Member
Joined
May 4, 2015
Messages
811
I know I saw a dynamic drop down on you tube, I think it took about 10 steps to do it...
 

mick0005

Active Member
Joined
Feb 21, 2011
Messages
406
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
ABCDEFG
1WeekCountryScoreWeekWeek1
2Week1China100
3Week1Hong Kong95Row LabelsSum of Score
4Week1India90China1
5Week1Indonesia85Hong Kong2
6Week1Japan80India3
7Week1Korea75Indonesia4
8Week1Malaysia70Japan5
9Week1Philippines65Korea6
10Week1Singapore60Malaysia7
11Week1Taiwan55Philippines8
12Week1Thailand50Singapore9
13Week1Vietnam45Taiwan10
14Week2China45Thailand11
15Week2Hong Kong50Vietnam12
16Week2India55Grand Total
17Week2Indonesia60
18Week2Japan65
19Week2Korea70
20Week2Malaysia75
21Week2Philippines80
22Week2Singapore85
23Week2Taiwan90
24Week2Thailand95
25Week2Vietnam100

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,707
Messages
5,833,241
Members
430,199
Latest member
Petty queen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top