SOLVED

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

IMG_20230727_173208_2.jpg

Initials list sheet

IMG_20230727_173223__01.jpg

 

thank you!!!

4 Replies

@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.

@HansVogelaar

@HansVogelaar 

 

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

 

IMG_20230802_100958.jpg

best response confirmed by eliza965 (Copper Contributor)
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

@HansVogelaar 

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

1 best response

Accepted Solutions
best response confirmed by eliza965 (Copper Contributor)
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

View solution in original post