DATA VALIDATION

[smckenzie2016]

Hi,

I am trying to use Data Validation to block off any further entry within the column once a certain amount of specific entries have been reached. The spreadsheet is for staff to self roster, within the column the text 'LN' can only be entered on two occasions capping any further entry of this criteria. Any help would appreciated.

thanks Sharon

 

[hagia_sofia]

Hello, I would probably do it by something like inserting a Custom data validation that being IF COUNITF/SUM of "LN" = 2 then enter empty cell. The precise formula would depend on whether you are looking for the number of cells equal to "LN" or containing "LN". Also, is the goal to block further entries or further entries of "LN"?

 

[awoohaw]

Welcome to the forum Sharon.
With the following you will not get a drop down. But a data entry check.

You get this error when "ENTERING" the invalid data in a cell:

One thing about any kind of data validation, there are ways that data you don't want in a cell can get in there. Usually by pasting.
This is a copy & paste:

If you absolutely need the data to be edited on entry then a VBA solution is probably best to review the data a the change sheet event (any part of the worksheet changes). But this can be a resource hog if you have lots of calculations.

 

[smckenzie2016]

Hello, I would probably do it by something like inserting a Custom data validation that being IF COUNITF/SUM of "LN" = 2 then enter empty cell. The precise formula would depend on whether you are looking for the number of cells equal to "LN" or containing "LN". Also, is the goal to block further entries or further entries of "LN"?

Hello and thanks for the advice, my goal would be to block further entries of LN once two of them had been entered.

 

[smckenzie2016]

Welcome to the forum Sharon.
With the following you will not get a drop down. But a data entry check.
View attachment 111276
You get this error when "ENTERING" the invalid data in a cell:
View attachment 111277

One thing about any kind of data validation, there are ways that data you don't want in a cell can get in there. Usually by pasting.
This is a copy & paste:

View attachment 111278

If you absolutely need the data to be edited on entry then a VBA solution is probably best to review the data a the change sheet event (any part of the worksheet changes). But this can be a resource hog if you have lots of calculations.

Hey, thanks for the reply and advice I will give this a try.