Limiting the number of times an initial appears in a week to 3

Copper Contributor

Hi everyone, I am trying to make a desk booking excel.


It is sorted by number of working days and weeks each month. To book one of the desks, a person just needs to put their initials in the column. There is a sheet with the list of initials.


However, I would like to limit the number of bookings to 3 a week. i.e. for week 1, you can only book a maximum of 3 days even though theres 5 working days.


Can this be done through things such as data validation etc?


Desk Booking sheet


Initials list sheet



thank you!!!

4 Replies


The photos are hard to read, but let's say the first week is C6:F100.

Select this range. C6 should be the active cell in the selection.

On the Data tab of the ribbon, click Data Validation.

Select Custom.

Enter the formula   =COUNTIF($C6:$F6,C6)<=3

If you wish, enter text in the Input Message and Error Alert tabs.

Click OK.


Repeat for the other weeks, changing the formula as needed.

@Hans Vogelaar

@Hans Vogelaar 


Thanks for the quick reply!


It kinda works but there are some loopholes

For example, if you look at the row for Desk 2 and 3, I can input the person AP more than 3 times if I put their initials for day 1-3 in desk 3 and day 4 in desk 2.


If i try to put the person AP for day 1-4 in desk 3, the error pops up which is good.


But is there a way to prevent this issue?


Thanks for your help



best response confirmed by eliza965 (Copper Contributor)


Select C6.

On the Home tab of the ribbon, click Conditional Formatting > Manage Rules...

Select the rule and click Edit...

Change the formula to



@Hans Vogelaar 

thank you so much!

im currently working as an intern and being able to do this properly is a great help.

hope you have a good day! i appreciate it