"AND function" with 3 tests doesn't work in conditional formatting

[LightStrider]

Hi folks

I have a worksheet with a table to control class days of english teaching.

These are the columns:

Status | Customer | Class Code | Date | (...)


On another worksheet I have a structure of a calendar.

I would like to use conditional formatting to paint the backgrounds of the calendar cells (on worksheet 2) according to the information on the table (on worksheet 1):

If the status of the class is "OK", and if the class code is "1002" and if the date in the table is equal to the one in the calendar, I want the cell painted green.

If the status of the class is "CAN", the class code is "1002" and the date in the table is equal to the one in the calendar, I want the cell painted red.

​

I have tested constructing the following formula (quite long indeed) in a blank cell and it worked, but when I paste it to the formula field in conditional formatting, the cells don't get painted (It's translated from Portuguese, so I hope the function names are correct here):

=AND(MATCH(C5;Date;0);
INDEX(tbl_Classes;MATCH(C5;Date;0);3)=1002;
INDEX(tbl_Classes;MATCH(C5;Date;0);1)="CAN")


1) On the first line I check if the day in the table is the same as the calendar

2) On the second line I test if the Class Code in the table is "1002" on the same line as above

3) On the third line I test if the Status in the table is "CAN" on the same line as above

C5 is "July 13th" in Worksheet 2

As you can see, I have defined names for the table (tbl_Classes) and the date field in the table (Date).

Once again, the formula returns "TRUE" when I put it in a blank cell. However, when I paste the formula into the field in conditional formatting, the calendar cell does not get painted.

What does it mean?

 

[shemayisroel]

Hi & Welcome,


Have you tried the CF using referencing in the same sheet?