Forum Discussion

eliza965's avatar
eliza965
Copper Contributor
Aug 01, 2023

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

  • 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

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

    • eliza965's avatar
      eliza965
      Copper Contributor

      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

       

      • 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

Resources