list 如何根据Excel中的多个特定文本进行条件格式

How to conditional format based on multiple specific text in Excel

提问by AtomicDog

I have a spreadsheet that i use to determine when/what clients to contact when an issue arises. in the first workbook i insert a column every day and paste in information about any questionable habits from clients, including a client ID. unfortunately the data i am copying from also includes clients that are not to be contacted during an issue. i have a second page that has listed in column A all of the "dont check" client ids. is there a way to conditional format the original page to color any cells referencing a "dont check" client based upon the don't check listing in sheet 2?

我有一个电子表格，用于确定出现问题时联系什么客户。在第一个工作簿中，我每天插入一列并粘贴有关客户任何可疑习惯的信息，包括客户 ID。不幸的是，我从中复制的数据还包括在问题期间不会联系的客户。我有第二页在 A 列中列出了所有“不检查”客户端 ID。有没有办法根据第 2 表中的不检查列表对原始页面进行条件格式设置，以便为引用“不检查”客户端的任何单元格着色？

EDIT: there is more than just client ID in the first sheet cells. client id is just included. the format is [(last file received date)(Client Name)(Client ID)(Last X file received date) (Last Y file received date)] all in one cell.

编辑：第一个工作表单元格中不仅仅是客户 ID。仅包含客户端 ID。格式为 [(last file received date)(Client Name)(Client ID)(Last X file received date) (Last Y file received date)] 全部在一个单元格中。

In theory what i would like is to go to conditional formatting, highlight cell rules, text that contains... then select A1-A45 on Sheet2 and click okay. obviously this is not possible. an error shows up stating "this type of reference cannot be used in a Conditional Formatting formula. Change the reference to a single cell, or use the reference with a worksheet function such as =SUM(A1:E5)

从理论上讲，我想要的是条件格式，突出显示单元格规则，包含的文本...然后在 Sheet2 上选择 A1-A45 并单击确定。显然这是不可能的。出现错误，指出“这种类型的引用不能用于条件格式公式。更改对单个单元格的引用，或将引用与工作表函数一起使用，例如 =SUM(A1:E5)

Thanks in advance.

提前致谢。

回答by Jerry

You can use MATCHfor instance.

MATCH例如，您可以使用。

1. Select the column from the first cell, for example cell A2 to cell A100 and insert a conditional formatting, using 'New Rule...' and the option to conditional format based on a formula.

2. In the entry box, put:

   =MATCH(A2, 'Sheet2'!A:A, 0)
   

3. Pick the desired formatting (change the font to red or fill the cell background, etc) and click OK.

1. 从第一个单元格中选择列，例如单元格 A2 到单元格 A100 并插入条件格式，使用“新规则...”和基于公式的条件格式选项。

2. 在输入框中，输入：

   =MATCH(A2, 'Sheet2'!A:A, 0)
   

3. 选择所需的格式（将字体更改为红色或填充单元格背景等），然后单击“确定”。

MATCHtakes the value A2from your data table, looks into 'Sheet2'!A:Aand if there's an exact match (that's why there's a 0at the end), then it'll return the row number.

MATCHA2从您的数据表中获取值，查看'Sheet2'!A:A是否有完全匹配（这就是0末尾有 a 的原因），然后它将返回行号。

Note: Conditional formatting based on conditions from other sheets is available only on Excel 2010 onwards. If you're working on an earlier version, you might want to get the list of 'Don't check' in the same sheet.

注意：基于其他工作表条件的条件格式仅适用于 Excel 2010 及更高版本。如果您使用的是早期版本，您可能希望在同一张工作表中获得“不检查”列表。

EDIT: As per new information, you will have to use some reverse matching. Instead of the above formula, try:

编辑：根据新信息，您将不得不使用一些反向匹配。而不是上面的公式，尝试：

=SUM(IFERROR(SEARCH('Sheet2'!$A:$A, A2),0))

回答by John Bustos

Suppose your "Don't Check" list is on Sheet2 in cells A1:A100, say, and your current client IDs are in Sheet1 in Column A.

假设您的“不检查”列表位于单元格中的 Sheet2 上A1:A100，例如，您当前的客户端 ID 位于 A 列的 Sheet1 中。

What you would do is:

你会做的是：

1. Select the whole data table you want conditionally formatted in Sheet1
2. Click Conditional Formatting> New Rule> Use a Formula to determine which cells to format
3. In the formula bar, type in =ISNUMBER(MATCH($A1,Sheet2!$A$1:$A$100,0))and select how you want those rows formatted

1. 选择要在 Sheet1 中有条件地格式化的整个数据表
2. 点击Conditional Formatting> New Rule>Use a Formula to determine which cells to format
3. 在公式栏中，输入=ISNUMBER(MATCH($A1,Sheet2!$A$1:$A$100,0))并选择您希望这些行的格式

And that should do the trick.

这应该可以解决问题。