使用Excel中的条件格式链接到另一个工作簿

How can i use conditional formatting to extract data from other workbook?

如何使用条件格式从其他工作簿中提取数据？

I tried:

我试过了：

=IF(LEFT(B26,3)="SSS",VLOOKUP(B26,'XXX.xls!$A$4:$E$119,4,FALSE))

But I get this error message:

但我收到此错误消息：

You may not link to other workbook via conditional formatting

您可能无法通过条件格式链接到其他工作簿

What can I do with this situation? People out there mentioned "define name" but I'm not sure how can it be done.

这种情况怎么办？那里的人提到了“定义名称”，但我不确定如何做到。

2 个解决方案

#1

Lets call the workbook where you want to enter the conditional format as MyWbk and the other one SrcWbk. This instructions are for excel 2007. Your mileage may vary.

让我们调用您想要输入条件格式为MyWbk的工作簿，另一个调用SrcWbk。此说明适用于excel 2007.您的里程可能会有所不同。

Open both Workbooks

打开两个工作簿
In MyWbk go to the Formulas Menu, and select Define Name

在MyWbk中，转到“公式菜单”，然后选择“定义名称”
Enter in Name > testname

输入名称> testname
In the "Refers to" field, select click on the range selector (at right) and when the selector opens, select SrcWkb and then the cell you want as source for the format condition.

在“引用”字段中，选择单击范围选择器（在右侧），当选择器打开时，选择SrcWkb，然后选择要作为格式条件源的单元格。
Accept the name creation

接受名称创建
Go to the cell where you want to enter the conditional format

转到要输入条件格式的单元格
Select the Home Menu / Conditional Formatting / Manage Rules

选择主菜单/条件格式/管理规则
Enter New Rule

输入新规则
Select "Use a formula to determine which cells to format"

选择“使用公式确定要格式化的单元格”
Enter the formula you want, using the defined name. For example =IF(testname=1,TRUE)

使用定义的名称输入所需的公式。例如= IF（testname = 1，TRUE）
Select the desired formatting

选择所需的格式
Test the formula changing the value in SrcWbk

测试公式更改SrcWbk中的值
SAVE SrcWbk

SAVE SrcWbk

You are done!

你完成了！

#2

I just ran into this. The limitation is still there in Excel 2013. But there is a workaround.

我刚碰到这个。 Excel 2013中仍然存在限制。但是有一种解决方法。

The limitation only exists in the Conditional Formatting editor. The conditional formatting itself works just fine.

该限制仅存在于条件格式编辑器中。条件格式本身就可以正常工作。

So in the editor, you can't refer to a defined range that refers to another workbook. You have to create the named range referring to your current workbook, define the conditional formatting that uses it, then when you are finished debugging it, change the defined range to the other workbook.

因此，在编辑器中，您不能引用引用另一个工作簿的已定义范围。您必须创建引用当前工作簿的命名范围，定义使用它的条件格式，然后在完成调试后，将定义的范围更改为其他工作簿。

This isn't as bad as it sounds because it is easier to debug your formatting within the same sheet anyway. This is only really a pain when you want to go back and change your formatting later.

这并不像它听起来那么糟糕，因为无论如何在同一张表中调试格式更容易。当您想要稍后返回并更改格式时，这真的很痛苦。

#1