Conditional formating using several workbooks

**Urkburklurk** · 08-10-2009, 04:13 AM

I have two different workbooks (Master and current), both of which have a column with product numbers.

What I want to do is to highlight the product numbers in the Current book if they also exist somewhere in the column in the Master book. That is, if numbers in book "current" also appear in "master", then that number in "current" will be highlighted.

Is this possible to do somehow?

**Palmetto** · 08-10-2009, 09:51 AM

No. Excel does not permit Conditional Formatting to directly reference cells in another workbook.

However, in the current workbook, using a helper colum, you can use the SUMPRODUCT function to look at the same columns of values in the Master workbook and use this returned value as the control for applying CF to the current workbook.

Example:
Column-A in Current holds values and you want to check Column-A in Master, then something like this used in a helper column:
=SUMPRODUCT(($A$1:A10=A1)*([Book2]Sheet1!$A$1:$A$10=A1))

**Urkburklurk** · 08-11-2009, 03:10 AM

Thank you for your help.

There is another issue however. The function you gave me, the way I interpret it, checks whether the value of a specific cell in Current has the same value as the opposing cell in Master. The values (product numbers) contained in the columns are not placed in order, and what I would like to be able to do is to check whether the value in, say A1 Current also exists anywhere in the range A1:A10 in Master. Is this manageable somehow?