Locking cells based on other cells values

Hi,

I'd like a little help with a roster system I have devised.

I have a workbook of 7 worksheets, each worksheet is a group. Within these groups there are only allowed to be 3 members of staff on day shift "d" or night shift "n".

At the bottom of the sheet it automatically calculates how many staff are on each shift. However, I'd like the column (Each column is a day) to automatically lock if the number of of the total shift = 3.

So this would mean two conditions, 1 for day shift and 1 for nightshift.

Condition 1 would be:
If the total day shift for Monday is 3, do not allow any more 'd' to be entered

Condition 2 would be:
If the total night shift for Monday is 3, do not allow any more 'n' to be entered

Thank you for any help, I am pretty stuck with this.

Screen Shot 2016-06-07 at 15.46.05.png

It would help a lot if you could post the workbook - with fictitious names, of course.

Could you please remove the protection or give us the password. It's difficult to troubleshoot and verify. Thanks.

I made a copy of your page into my own workbook. It should be enough to demonstrate the principle.

Each cell is controlled by a Data Validation. The data validation looks at one of 4 Lists: List_D_Y, List_D, List_N and List_Null. These are static named ranges defined on sheet 2.

There is a helper row (row 17), that has the formula: =IF(AND(D15<3,D16<3),0,IF(AND(D15<3,D16=3),1,IF(AND(D15=3,D16<3),2,3))).

This evaluates to 0 if both day and night shifts are less than 3, 1 if day is less than 3 but night is 3, 2 if day is 3 and night is less than 3 and 3 otherwise (both day and night are three).

The data validation on each cell is a list type that reads this range: =OFFSET(List_D_N,0,D$17) where D17 is the helper cell. So the range that is read is offset by zero if D17 is zero (meaning it reads range List_D_N), by 1 if D17 is 1 (days are available so it reads List_D) and so on.

Yes you can enter them to the lists on Sheet 2. However go to the name manager and extend the range of the lists. Actually only List_D_N needs to be extended. The other ranges aren't really used. Rather the offset from List _D_N is used.

You should also adjust the formulas in rows 15 and 16 to count NR against D and N.

In Cell D4 there is a data validation, set up as shown in the picture. You can copy -> paste special / validations to the other cells on the sheets.