Conditional formatting =AND

Yes. Use OR.

=AND(a, b, c, d)
This expression is true when a AND b AND c AND d are all true.

=OR(a, b, c, d)
This expression is true when any one (or more) of a OR b OR c OR d are true.

Originally Posted by t1o9n9y1

If say "WORKLOAD" is in cell A1 on sheet 1 and B1 on sheet 2 and it was also to say "WORKLOAD" on sheet 3 in cell C5, I want it to recognise that and colour the cell of sheet 3 a different colour. But, only if there is matching text between the four cells on both sheet 1, 2 and 3.

Does that make sense?

No, because in your first post you said

I am trying to use conditional formatting so that if any one of the four cells on sheet 1 match the cell on sheet 2, the cell on sheet 2 turns amber. However, whenever I use =AND, it requires all 4 cells to match the single cell on sheet 2.

Having the file will help, but it only if we understand what you want to do.

I have attached the file.

So there are five sheets, each representing a different day.

In cells, N4, N12, S4, S12 and AJ8, there are dropdown lists with 11 names.

If on Sheet 1, a name appears in any of those cells and then on Sheet 2 the same name
appears in any of those cells, using =MATCH and conditional formatting I have got the
names to flag up with a yellow fill.

However, where I am stuck is, if on the third sheet, the same name appears yet again in
any of those cells, so the same name over three sheets, I want the name to flag up with a red fill.

Sorry that my explanations have been pretty shoddy. I am not the best at explaining
things and I was trying to keep names and things out of the post. Thanks anyway.

This can certainly be done. I am going to restate your problem to make sure I understand.

First, you are talking about the first three sheets, but you have five sheets. You talk about "If on Sheet 1...then on Sheet 2...if on the third sheet" and then "the same name over three sheets" But do you really mean the same name over the first three sheets, or any three sheets?

Here is what I think you want:

1. Every sheet has the following cells that can contain a name: N4, N12, S4, S12 and AJ8
2. If the same name appears in any of those cells on at least three sheets, then highlight the name in red. It does not have to be the same cell on all sheets.

This is a bit of a challenge since it's a discontinuous range spread across several sheets. To solve these I have created a new sheet to do the heavy lifting, and conditional formatting refers to it. This sheet has formulas that count how many times each name occurs on all sheets.

I would highly recommend that you create a list of names somewhere and refer to that instead of typing out the list in each data validation rule. It will make it so much easier if you ever have to change that list.

Also, you are using a lot of merged cells which is usually a bad idea and makes all of this much harder.

Also I have not reviewed your existing conditional formatting rules but I suspect this is a very complicated solution that will be a nightmare if you ever have to change anything. I recommend redesigning your entire solution.

Originally Posted by t1o9n9y1

So if the same name appears over 3 consecutive sheets/days, so Mon, Tue & Wed or Tue, Wed & Thu, or Wed, Thu & Fri it flags up as red.

Well, no, that's not what I did. That's why I tried to explain back what I thought you wanted. If you need consecutive sheets, it needs a change. The good news is that the way I did it makes it easy to change.

Update attached.

I'll give some thought to your rule for yellow. It works fine but if you ever have to update the names it's going to be a headache.