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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

oldbrewer

Board Regular
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
810
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,052
Members
414,357
Latest member
Gemma_R

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