Hey everyone! I’m working on a Google Sheets file that’s shared with others. I need to put a cap on how many entries can go into certain columns. Is there a way to make it so that after 5 entries, the sheet won’t let anyone add more?
I want it to show a warning message if someone tries to put in a sixth entry. I’ve been messing around with data validation and the COUNTA function, but I can’t figure it out.
Has anyone done something like this before? What’s the best way to set it up? I’d really appreciate any tips or tricks you can share. Thanks for your help!
I experimented with a similar issue in a project where Google Sheets needed to enforce a cap on entries. I ended up using a custom Google Apps Script triggered by every edit to count the current entries in a range. When the script detected that the number of entries exceeded a set limit, it warned the user and prevented additional data entry. It took multiple rounds of testing to get the behavior smooth, but it eventually worked reliably. If scripting isn’t an option, a well-designed conditional formatting rule can serve as a visual cue for users about the limit being reached.
hey happydancer, i’ve solved this by using google apps script. data validation isn’t enough so i wrote a custom function to count entries and disable input after 5 entries. lmk if u want further info!
While data validation and COUNTA have limitations, there’s a workaround using custom formulas and conditional formatting. Create a helper column with a formula like =IF(COUNTA($A$2:$A)<=5, “OK”, “Limit Reached”). Then apply conditional formatting to your target column based on this helper column. Set the cell color to grey when the limit is reached. This visual cue alerts users without fully restricting input. It’s not foolproof, but it’s a simpler solution than scripting for most cases. Remember to protect the helper column to prevent tampering. This method has worked well in my shared projects, striking a balance between flexibility and control.