Forum Discussion
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 for each column I would then have to add conditional formatting to highlight those cells if any o these key works etc where present; 'ERT', 'BBC', 'Motor-OIL', etc...So is there a way to point to use a single reference point to do the same job.
The aim of the conditional formatting is just to change the cell colour if any one of these keys appears appear there. The list needs to be dynamic as it will expand.
Chis
- 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.
4 Replies
- 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)))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.
