Link to another workbook using conditional formatting in Excel

Question

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.

Solution

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.

1. Open both Workbooks

2. In MyWbk go to the Formulas Menu, and select Define Name

3. Enter in Name > testname

4. 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.

5. Accept the name creation

6. Go to the cell where you want to enter the conditional format

7. Select the Home Menu / Conditional Formatting / Manage Rules

8. Enter New Rule

9. Select "Use a formula to determine which cells to format"

10. Enter the formula you want, using the defined name. For example =IF(testname=1,TRUE)

11. Select the desired formatting

12. Test the formula changing the value in SrcWbk

13. SAVE SrcWbk

You are done!

Comments

[CJ Dennis]

I just discovered that you have to have the source workbook open in memory before opening the workbook with the conditional formatting. I.e., it won't read the values off the disk.