Conditional Formatting based on data in another spreadsheet

[michael.collins22]

I have a column of names. I am trying to conditionally format the names (Green or Red) based on data from another spreadsheet in the workbook. Is this possible?

 

[Oaktree]

Yes, but you'd have to first assign a named range.

For example, you can't use ='Other Sheet'!A1>5 as a criterion for your conditional format, but you could set (via insert --> name --> define) the named range my_cell to refer to ='Other Sheet'!A1 and use =my_cell > 5 as your criterion.

 

[michael.collins22]

Thanks so much. Absoultely Brillant.

 

[michael.collins22]

OK, the more I know, the more questions I have.

What I am trying to do is turn the background color of a cell one color or another based on if a text string is in a column of data in another spreadsheet in the same workbook.

You showed me how to Define a cell and use that one cell as criteria in my conditional formatting. What about trying to Define a group of cells and including that Definition into a more complicated formula than a simple logic one? Is that possible in Conditional Formatting?

Sorry to make things more difficult...

 

[Oaktree]

Set the named range my_range to refer to =Sheet2!$A$1:$A$100 or whatever your range is.

Then, define your conditional formatting for Sheet1!A1 as: Formula is =ISNUMBER(MATCH(A1,my_range,0)) and apply the desired formatting.

 

[michael.collins22]

Thanks so much for your help. It appears to be working. I'll post back if I run into anymore complications. How come you used the Function ISNUMBER and not ISTEXT?

 

[Oaktree]

MATCH(A1,my_range,0) will either return a number (the position of the match) or an #N/A error (if no match is found). So, ISNUMBER appropriately maps those possibilities to TRUE/FALSE values for use as the basis for the conditional formatting criterion.

 

[michael.collins22]

Thanks for explaining. I am trying to get to the point where I am (more or less) self-sufficient so I'm trying to understand why the formulas work.