A nice new feature in Excel 2010 is the ability to refer to a different worksheet when creating conditional formatting and data validation. Let's take a look at how the conditional formatting from different sheet feature works, and create a workaround for older Excel versions.

Excel 2010中的一项不错的新功能是能够在创建条件格式和数据验证时引用其他工作表。 让我们看一下来自不同工作表功能的条件格式如何工作，并为较早的Excel版本创建解决方法。

比较两张纸上的单元格 (Compare Cells on Two Sheets)

In this example, you'd like to highlight the cells on Sheet1, if they're higher than the matching cell on Sheet2.

在此示例中，如果要高于Sheet2上匹配的单元格，则要突出显示Sheet1上的单元格。

要突出显示Excel 2010中的单元格，请执行以下操作： (To highlight the cells in Excel 2010:)

• Select cells A1:C8 on Sheet1, with A1 as the active cell
  在Sheet1上选择单元格A1：C8，以A1为活动单元格
• On the Ribbon, click the Home tab, and click Conditional Formatting
  在功能区上，单击“主页”选项卡，然后单击“条件格式”
• Click Highlight Cell Rules, then click Greater Than
  单击突出显示单元格规则，然后单击大于
• In the Greater Than dialog box, click in the cell reference box
  在“大于”对话框中，单击单元格引用框
• Click on the tab for Sheet2
  单击选项卡上Sheet2
• Select cell A1 on Sheet2, and click OK
  在Sheet2上选择单元格A1，然后单击确定

The cells on Sheet1 are now highlighted if their value is higher than the matching cell on Sheet2.

如果Sheet1上的单元格的值高于Sheet2上的匹配单元格，则现在突出显示。

早期版本的条件格式解决方法 (Conditional Formatting Workaround for Earlier Versions)

In earlier versions of Excel, you can't refer to another sheet in conditional formatting or data validation. With data validation, you can't even click on the other sheet, to try and create a reference.

在早期版本的Excel中，您不能在条件格式或数据验证中引用其他工作表。 使用数据验证，您甚至无法单击另一张纸来尝试创建参考。

In conditional formatting, you can enter the reference in the dialog box, but then Excel yells at you.

在条件格式中，可以在对话框中输入引用，但是Excel会对您大吼大叫。

However, you can refer to named ranges anywhere in the workbook, in both conditional formatting and data validation, so you can use that as a workaround.

但是，您可以在条件簿格式和数据验证中引用工作簿中任何位置的命名范围，因此可以将其用作解决方法。

要突出显示Excel 2007和早期版本中的单元格，请执行以下操作： (To highlight the cells in Excel 2007 and earlier versions:)

• On Sheet2, name cell A1 as DataStart02

  在Sheet2上，将单元格A1命名为DataStart02

• Select cells A1:C8 on Sheet1, with A1 as the active cell
  在Sheet1上选择单元格A1：C8，以A1为活动单元格
• On the Ribbon, click the Home tab, and click Conditional Formatting
  在功能区上，单击“主页”选项卡，然后单击“条件格式”
• Click Highlight Cell Rules, then click Greater Than
  单击突出显示单元格规则，然后单击大于
• In the Greater Than dialog box, click in the cell reference box
  在“大于”对话框中，单击单元格引用框
• Type an OFFSET formula that refers to the named range on Sheet2. This will refer to the cell on Sheet2 that is in the same row and column as the active cell.
  键入一个OFFSET公式，该公式引用Sheet2上的命名范围。 这将引用Sheet2上与活动单元格相同的行和列中的单元格。

=OFFSET(DataStart02,ROW()-1,COLUMN()-1,1,1)

= OFFSET(DataStart02，ROW()-1，COLUMN()-1,1,1)

• Click OK
  点击确定

The cells on Sheet1 are now highlighted if their value is higher than the matching cell on Sheet2.

如果Sheet1上的单元格的值高于Sheet2上的匹配单元格，则现在突出显示。