Limit the number of values' occurrences

the only way i could think of coming close to this is to utilize a helper column in column B. This will count the amount of time above the # has occured. It will return ERROR in the cell next to column A where the count has gotten to high. Mind you they can still input a 2,3, or 4 in column A but will keep saying ERROR in column B.
See my attached workbook.
Dropdown lists are in A1:A20
Helper formula is in B1:B20.

Formula:

Please Login or Register  to view this content.

Next time, please attach an Excel file, rather than a non-editable picture of one.

Does this do what you want? If so, I'll epxlain.

Sheet Choices.

Column A enter the values you want to allow in the dropdown (DD) list.

Column B enter the numbe rof each one permitted.

Column C counts the number already used in the YELLOW cells in ENTRY (NB formula has been changed):
=COUNTIF(Entry!$A$3:$A$11,A2)

copied down.

Column D (copied down)
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$4)/($B$2:$B$4>$C$2:$C$4),ROWS($1:1))),"")

Red - return the values in column A
Orange - from these rows
Cyan - started from the one with the smallest row number
Blue - that meets the criterion - number allowed is greater than the number used
Purple - one at a time, starting from the first one in col A, then the second, etc.


To avoid having blanks in the drop down box, I sed a Named range (called namelist) to select ONLY the non-blank values in column D CTRL-F3 to view/edit

=Choices!$D$2:INDEX(Choices!$D$2:$D$4,SUMPRODUCT(--(LEN(Choices!$D$2:$D$4)>0)))

Basically, it starts from D2 (red) and looks down to D4 (orange) and returns the values in D2:D4 which have a length >0.

Finally, in sheet ENTRY, set up data validation in the yellow cells: Data/DataVailidation/List/=Namelist.

Use the file attached here, as opposed to the earlier one.

If you need more choices, etc, modify the bits in red (below) to suit.

=COUNTIF(Entry!$A$3:$A$11,A2)

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$4)/($B$2:$B$4>$C$2:$C$4),ROWS($1:1))),"")


Choices!$D$2:INDEX(Choices!$D$2:$D$4,SUMPRODUCT(--(LEN(Choices!$D$2:$D$4)>0)))

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

Thank you so much! It works perfectly.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.