Excel：条件格式化，检查列是否包含其他表单中的文本 [英] Excel: conditional formatting, check if column contains text from other sheet

问题描述

第一页：

First name  Last name       Role
Valeria     Bianchi         Admin
Evelise     Verdi           Teacher
Daniele     Rossi           Teacher
Annamaria   Neri            Admim
Anna        Gialli          Manager
Anna Bella  Proietti        Teacher
Simona      Ciaociao        Manager
Elisabetta  Lilla           Admin

第二张表：

Email       Name
[e-mail]  Valeria Bianchi
[e-mail]  Daniele Rossi   
[e-mail]  Annamaria Neri  
[e-mail]  Anna Gialli 
[e-mail]  Simona Ciaociao
[e-mail]  Elisabetta Lilla

现在我有一个条件格式的规则，如果角色等于教师，则第一页中的行：
= $ C2：$ C200 =Teacher。如果角色等于教师，我想添加另一个颜色为另一个颜色的行的规则，第二个表在名称列中显示其名称（仅包含 Daniele Rossi 应该符合这个规则）。

Now I have a rule for conditional formatting that colors the rows in the first sheet if Role is equal to Teacher: =$C2:$C200="Teacher". I'd like to add a second rule that colors the row of another color if Role is equal to Teacher AND in the second sheet is present its name in the Name column (only the row with Daniele Rossi should match this rule).

希望很清楚..：）

推荐答案

这样做会伎俩。您将必须修复单元格引用以匹配您的工作表。

This will do the trick. You will have to fix the cell references to match your sheet.

还要确保您有正确的规则顺序，如果有匹配则停止处理更多规则。

Also make sure you have the order of the rules correctly and stop processing more rules if there is a match.

连接在第一张表中添加第一张和最后一列
Sheet2！$ a $ 1：$ a $ 6是第二张表中名称的范围

The concatenate adds first and lastname columns in first sheet Sheet2!$a$1:$a$6 is the range for the names in the 2nd sheet

=AND(MATCH(CONCATENATE(A2;" ";B2);Sheet2!$A$1:$A$6;0); C2="Teacher")

这篇关于Excel：条件格式化，检查列是否包含其他表单中的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！