# Limit number of times a value may be selected from a drop down menu and count them?

#### cmajewsk

##### New Member
I have drop down I am trying to count and limit the number of times certain values can be selected. The worksheet is setup like this:

Name Role
Alex Clerk
Stacey Developer
Mike Analyst
Chris Pool

The Position column has the drop down of job roles we want to put them in, however there are more names and than roles, except for 1 role that is "Pool" that should be unlimited. So how can I make is so when you select a role from the down it puts the count of that role somewhere on the sheet and only allows a certain amount of each role before showing an error like :You have exceeded the number of "role name".

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### jasonb75

##### Well-known Member
Perhaps with something like this?
Book2 (version 1).xlsb
BCDEFG
1RoleRoleLimitValidation list
3AnalystDeveloper3Developer
4Analyst1Pool
5Pool10000
Sheet17
Cell Formulas
RangeFormula
G2:G5G2=IFERROR(INDEX(\$D:\$D,AGGREGATE(15,6,ROW(\$D\$2:\$D\$5)/(COUNTIF(\$B\$2:\$B\$20,\$D\$2:\$D\$5)<\$E\$2:\$E\$5),ROWS(G\$2:G2))),"")
Cells with Data Validation
CellAllowCriteria
B2:B5List=OFFSET(\$G\$2,0,0,COUNTIF(\$G\$2:\$G\$5,"?*"),1)

Replies
2
Views
175
Replies
0
Views
401
Replies
5
Views
372
Replies
7
Views
421
Replies
1
Views
120

1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

### 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.

### Which adblocker are you using?

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

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