How can I limit the number of dropdown entries in a column in Google Sheets

Question

I have column A with a dropdown menu and a single option of "Yes". I want to limit the number of entries to 20, and ideally when that number is hit there is a pop up (similar to how data validation error works) telling the user why they can't select any more.

Answer 1

Unfortunately, data validation share the same feature with the function of dropdown list, so it seems no way to combine them together unless using Google Apps Script.

To limit A:A contains only 20 "Yes", you may use a custom formula

=COUNTIF(A:A, "Yes")<=20

in the data validation setting sidebar. validation sidebar with a formula

Below is a spreadsheet as a demo with 5 as an upper bound: https://docs.google.com/spreadsheets/d/1fI7tndMfIRxuCtpuNJvAs54YQc_qiW5qI7-VUoUbTZo/edit#gid=0

Answer 2

How about you try this method:

1st, Create a backend tab (Dropdown List Backend), and a column (Dropdown List) that will contain the values for your dropdown. Note that each of the cells in this column should only correspond to its equivalent dropdown cell in Dropdown Input Tab (Please see the sheet).

2nd, We add the formula below to the Dropdown List Column (See the image below):

=ArrayFormula(IF('Dropdown Input Here'!A2:A11="Yes","Yes",IF(COUNTIF('Dropdown Input Here'!$A$2:$A$11,"Yes")<5,"Yes",)))

This formula checks if its corresponding dropdown is "Yes." If true, then just return "Yes." Otherwise, we'll count the no. of "Yes" from the Dropdown Column. If the count is below the limit (in my example, I set the limit to 5), we return "Yes." Otherwise, we return Blank.

Finally, We link the the cells to their own dropdowns. To do that, we just highlight the column of the dropdowns and select the first cell in the Dropdown List Column from the Backend and make sure to make the row part of the reference Relative (No $). Check if A2 Dropdown is linked to A2 from the backend, A3 Dropdown is linked to A3 Backend and so on.

Here's the sheet link: https://docs.google.com/spreadsheets/d/1wt38gES04IFbmEHNUBDqj32JHfEdnfqb0MLXQ3jntjo/edit#gid=2100307022

Note: Just expand the ranges in the formula to 200 (e.g., A2:A200).

Also, avoid copy-paste and dragging the cells with the dropdowns because it can break the links.