Forum Discussion
eliza965
Aug 01, 2023Copper 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!!!
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
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.
- eliza965Copper Contributor
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
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