How can I define how many times a digit can be used in a column

Goal: so that with a formula (1) I can define how may times a "5" (perfomance rating for how well you do your job) can be entered into a column (Performane Rating). The number (formula 1) will be dependant on the number of empployees that render on the sheet. So for example: I have 20 employees on a sheet, 70% can have a 3(average employee), 15% can have a 3 (high performer) and another 3 (slackers can have a 1). I have already figured out how to come up with the numbers allowed based on the employee population.

Needs: I need to figure out a way so that front end user are unable to to chose more than is allocated for the employee population on the sheet.

Details: Data is feed in using a data source (Oracle, SQL) and compiled using the XML definitions available in 2007

I have read thru some of the other similiar threads and have seen some heavy coding techniques that are outside of my skill set. I'm trying to stay away from using a the Excel 'List' feature since it would be difficult to use since the number of employees could be significantly different. Just looking for some innovate ideas...


Thanks in advance to all who respond and I hope I have provided enough information for you expert users to see what I am trying to attempt.

Matt

Right click on the Tab for sheet1 and choose View Code...
The code will fire every time the data in column B is changed. It will display a warning message if too many 5s have been entered in the column. The 5 limit is based on 0.15 times the number of employees, rounded up to the next whole number, using the CEILING() function. The code will then clear the last 5 entered.

The code

Please Login or Register  to view this content.

I non-invasive (aka no macro) option would be to use conditional formatting. It won't prevent the user from entering too man 5s put it would give a visual alert. The attached spreadsheet shows both methods. Columns B uses the macro column C uses the conditional formatting method. Under the current set up all 5s will be highlighted once there are too many 5s.

WOW!!!! Thank you very much. I am going to try to incorporate this into the spreadsheet I am working on which of course is more complicated. If it I have trouble I may reach out to you and provide you with the sheet I am actually working on if you wouldn't mind looking at the real data sheet, again you are the MAN!

Sure - reach away.