Forum Discussion
Solved
Conditional Formating, a single reference point
Hi Is there a way to reference a list in conditional formatting, for example, I want have a list of key words or abbreviations like ERT, BBC, Motor-OIL. The list will expand as time goes on. So...
- MATCH tries to find an item in a list. If successful, it returns the position within that list where the item was found. If not successful, it returns an error: #N/A.
ISERROR simply returns TRUE if a formula returns an error.
NOT reverses a boolean value so TRUE becomes FALSE and vice versa.
So NOT(ISERROR(MATCH(..))) returns TRUE if a match was found, which then tells Excel to color the cell.
1. Format the list as a table (this ensures the CF will auto-adjust with the size of the list)
2. If you want all the hits to get the same color you can set the CF to custom and use a formula like:
=NOT(ISERROR(MATCH(A2,Sheet2!$A$2:$A$10,0)))
2. If you want all the hits to get the same color you can set the CF to custom and use a formula like:
=NOT(ISERROR(MATCH(A2,Sheet2!$A$2:$A$10,0)))
That is great, sorry its fabulous works nicely. Are able to explain to me what the formula is doing? I will admit Match(), and also Index(), are some of the functions I have avoided as they look too complicated and I have not seen NOT() before.
Chris
- MATCH tries to find an item in a list. If successful, it returns the position within that list where the item was found. If not successful, it returns an error: #N/A.
ISERROR simply returns TRUE if a formula returns an error.
NOT reverses a boolean value so TRUE becomes FALSE and vice versa.
So NOT(ISERROR(MATCH(..))) returns TRUE if a match was found, which then tells Excel to color the cell.ISERROR simply returns TRUE if a formula returns an error.
NOT reverses a boolean value so TRUE becomes FALSE and vice versa.
The old double-negative-makes-a-positive trick. Nicely done! 🙂
