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?
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.
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?
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?
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.
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.