Conditional formatting; Format Fields based on values in another file

[doverfelt]

Is it possible to setup conditional formatting on a field in one spreadsheet based on the value in a cell in a completely different workbook? here's my situation...

I have one file (File1.xlsx) with a dashboard of red, yellow green formats via a dropdown list of R, Y and G
I have a second file (File2.xlsx) showing a status on various reports; Not Started, In work, In Review and Complete

What I'd like to do is...
Format the status cell in the 1st file in red, if the value in the 2nd file is "Not Started"
Format the status cell in the 1st file in yellow if the value in the 2nd file is "In Work" OR "In Review"
Format the status cell in the 1st file in green, if the value in the 2nd file is Complete

Can I use a formula under Conditional Formatting, and actually look up the field value in a 2nd excel file? I've searched this forum and others with examples of this, so I'm not even sure it's an "allowed" option at this point....

Any thoughts?

DO

 

[Joe4]

Conditional Formatting isn't really designed to work off of data in separate workbooks.
There are a few "workarounds" which can be seen here: https://stackoverflow.com/questions...orkbook-using-conditional-formatting-in-excel