SOLVED

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

Copper Contributor

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

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

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

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.

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

@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)
Solution

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

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

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

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