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

[eliza965]

 

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!!!

[#2]

@eliza965 

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.

[#3]

@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

 

[#4 · Accepted solution]

@eliza965 

Select C6.

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

Select the rule and click Edit...

Change the formula to

=COUNTIF($C$6:$F$100,C6)<=3

[#5]

@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