Forum Discussion
Solved
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 c...
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.
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
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
