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

[cmajewsk]

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

 

[jasonb75]

Perhaps with something like this?

Book2 (version 1).xlsb
	B	C	D	E	F	G
1	Role		Role	Limit		Validation list
2	Developer		Admin	2		Admin
3	Analyst		Developer	3		Developer
4			Analyst	1		Pool
5			Pool	10000
Sheet17

Cell Formulas
Range	Formula
G2:G5	G2	=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
Cell	Allow	Criteria
B2:B5	List	=OFFSET($G$2,0,0,COUNTIF($G$2:$G$5,"?*"),1)