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

cmajewsk

New Member
Joined
Sep 26, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
Jim Admin
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".
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,934
Office Version
  1. 365
Platform
  1. Windows
Perhaps with something like this?
Book2 (version 1).xlsb
BCDEFG
1RoleRoleLimitValidation list
2DeveloperAdmin2Admin
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,610
Members
414,080
Latest member
penguin23

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