How can I use data validation to restrict specific combinations of numbers in a column?

I work in admin at one of my local hospital, due to COVID, all visits are on an appointment-only basis. We can't have more than one visitor per room per 24h. The gov considers dual beds rooms to be one room so only one visitor per 24h is allowed in dual bed rooms.

Because of staff permutation and high volume of calls, errors and duplicates are occurring, I am working on a table to mitigate the hassle.

Dual bed rooms use the same format than single bed ones. In a dual beds rooms, bed 1 and bed 2 are assigned two distinct rooms numbers.

I use this formula in a custom data validation rule to prevent duplicates (take note that I'm using french, it's the COUNTIF function):

=NB.SI(C:C,C6)=1

I now need to add a rule to prevent specific combinations of numbers in a column. ex: 1004 and 1006 in the same column would trigger an error message.

I wrote this formula (ET=AND NB.SI=COUNTIF):

=ET(NB.SI(C:C,1004)=1,NB.SI(C:C,1006)=1)

How do I combine them into a single data validation rule?

The goal is to create a foolproof work tool for bottom end employees and promote staff permutation.

How about this:

set up a list of prohbited combinations (here in U1 and U2). Use this as conditional formattning:

IF(ISNA(MATCH($C6,$U$1:$U$2,0)),FALSE,SUM(COUNTIF($C$6:$C$94,$U$1:$U$2))>1)

If you add another room to the list (e.g. 1984), extend the bits in red (or we can get Excel to do that for you if they are liable to change regularly), then any combination 1004, 1006 and 1984 where >1 is present will trigger a red flag.

HOWEVER>>> I am not entirely sure that that is what you really want. We'll see...

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.