Excel conditional formatting formula to compare two tables side-by-side

I have two tables side by side (equal dimensions), and I want to highlight the cells that are equal. (The two tables are side-by-side because Excel 2007 doesn't allow Conditional Formatting across sheets).

Supposing that table B is offset from table A by 16 rows, 开发者_运维问答I thought my formula would be:

=INDIRECT(CELL("address"))=OFFSET(INDIRECT(CELL("address")),0,16)

(For the "Format values where this formula is true" box).

But I'm in error. What would be the correct formula to enter?

To use the Cell function, you have to specify the 2nd, optional, "Reference" argument. From Excel 2010 help:

"If [the reference argument is] omitted, the information specified in the info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper left cell of the range."

Better yet, don't use the calculation-intensive Indirect function. Assuming you've highlighted the left-hand table data rows and the active cell is A2, enter this in your Conditional Formatting, making sure that it uses relative referencing (no "$" symbols):

=A2=OFFSET(A2,0,16)

EDIT - Even better yet, as commented by @chris neilsen, eliminate the also calculation-intensive Offset function for the equally effective:

=A2=Q2