Conditional Formatting with COUNTIFS

[Darren_workforce]

I have a file that has 3 sheets: 8-45, 9-15 and 'did not attend'. These reference meeting times since you can't use colons in sheet names.

Within both '8-45' and '9-15' Column A on each has a list of participants who attended the respective Teams meeting.

Within 'Did not attend', Column A has a list of all department employees alphabetically.
I have setup CF to find any names within '8-45' and '9-15' and will shade them (yellow if it matters) along Column A in the 'Did not attend' sheet.

=COUNTIFS('8-45 lineup'!$A$11:$A$150,A1:A100)
=COUNTIFS('9-15 lineup'!$A$11:$A$150,A1:A100)

I was not sure how to setup the formula to reference both sheets at once so I just setup 2 separate CFs. So if is possible to combine both of those into 1 CF formula, I'm open.

My question is: can the formula be adjusted so that instead of highlighting the names it finds in the other 2 sheets, is it possible to highlight any names not found in either of the other 2 sheets? I tried using <>, <=, >= but I have a feeling since the formula references an entire range, maybe those don't work.

Thank you for all help in advance!!

 

[DanteAmor]

Hi @Darren_workforce , thanks for posting on the forum.

Assuming the following scenario.
8-45 sheet

Dante Amor
	A
11	ana
12	carlos
13	daniel
8-45

9-15 sheet

Dante Amor
	A
11	gloria
12	maría
13	raúl
9-15

did not attend sheet

Dante Amor
	A
1	ana
2	carlos
3	daniel
4	gloria
5	laura
6	maría
7	pedro
8	raúl
9	sonia
did not attend

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A9	Expression	=(COUNTIF('8-45'!$A$11:$A$150,$A1)+COUNTIF('9-15'!$A$11:$A$150,$A1))=0	text	NO

is it possible to highlight any names not found in either of the other 2 sheets?

In the above scenario, laura, pedro and sonia are not on any of the sheets (8-45 and 9-15)


--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[Darren_workforce]

Thank you @DanteAmor! That did exactly what I needed it to do!! Thank you again!!!