Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Values w/conditional formatting
I am managing a do-not-call list with a large contact
database. How do I establish a poitive/negative match from source data. eg. Cell D7 value is: 212-555-1212. The source worksheet has a list of all phone numbers in Column A. So, if the value of Cell D7 matches a value in source worksheet Column A, I can conditionally format it. Thanks in advance. |
#2
|
|||
|
|||
You can use the FORMULA IS option under conditional formatting. Use a
formula like =COUNTIF($A$1:$A$999,B1)0 if you're referencing another worksheet, you must use indirect =COUNTIF(INDIRECT("Sheet1!$A$1:$A$999"),B1)0 "Fred Timmons" wrote in message ... I am managing a do-not-call list with a large contact database. How do I establish a poitive/negative match from source data. eg. Cell D7 value is: 212-555-1212. The source worksheet has a list of all phone numbers in Column A. So, if the value of Cell D7 matches a value in source worksheet Column A, I can conditionally format it. Thanks in advance. |
#3
|
|||
|
|||
Use conditional formatting with a formula of
=ISNUMBER(MATCH($D$7,$A:$A,0)) and select a colour for the format. If that source sheet is a different sheet to D7, then you will need to give the cells in column A a workbook name and refer to that in the formula. -- HTH RP (remove nothere from the email address if mailing direct) "Fred Timmons" wrote in message ... I am managing a do-not-call list with a large contact database. How do I establish a poitive/negative match from source data. eg. Cell D7 value is: 212-555-1212. The source worksheet has a list of all phone numbers in Column A. So, if the value of Cell D7 matches a value in source worksheet Column A, I can conditionally format it. Thanks in advance. |
#4
|
|||
|
|||
Just
=MATCH($D$7,$A:$A,0) should suffice. Bob Phillips wrote: Use conditional formatting with a formula of =ISNUMBER(MATCH($D$7,$A:$A,0)) and select a colour for the format. If that source sheet is a different sheet to D7, then you will need to give the cells in column A a workbook name and refer to that in the formula. |
#5
|
|||
|
|||
I agree, but that depends upon an error always resolving as False. That is
something that doesn't seem right to me, and so I prefer not to rely upon it. "Aladin Akyurek" wrote in message ... Just =MATCH($D$7,$A:$A,0) should suffice. Bob Phillips wrote: Use conditional formatting with a formula of =ISNUMBER(MATCH($D$7,$A:$A,0)) and select a colour for the format. If that source sheet is a different sheet to D7, then you will need to give the cells in column A a workbook name and refer to that in the formula. |
#6
|
|||
|
|||
Thank you. How would I repeat this conditional format for
a series of cells (D7 to D500)? -----Original Message----- Use conditional formatting with a formula of =ISNUMBER(MATCH($D$7,$A:$A,0)) and select a colour for the format. If that source sheet is a different sheet to D7, then you will need to give the cells in column A a workbook name and refer to that in the formula. -- HTH RP (remove nothere from the email address if mailing direct) "Fred Timmons" wrote in message ... I am managing a do-not-call list with a large contact database. How do I establish a poitive/negative match from source data. eg. Cell D7 value is: 212-555-1212. The source worksheet has a list of all phone numbers in Column A. So, if the value of Cell D7 matches a value in source worksheet Column A, I can conditionally format it. Thanks in advance. . |
#7
|
|||
|
|||
Thank you. How would I repeat this conditional format for
a series of cells (D7 to D500)? -----Original Message----- I agree, but that depends upon an error always resolving as False. That is something that doesn't seem right to me, and so I prefer not to rely upon it. "Aladin Akyurek" wrote in message ... Just =MATCH($D$7,$A:$A,0) should suffice. Bob Phillips wrote: Use conditional formatting with a formula of =ISNUMBER(MATCH($D$7,$A:$A,0)) and select a colour for the format. If that source sheet is a different sheet to D7, then you will need to give the cells in column A a workbook name and refer to that in the formula. . |
#8
|
|||
|
|||
Select all the cells D7:D500, and then go into CF and use a formula of
=ISNUMBER(MATCH($D7,$A:$A,0)) i.e. row relative -- HTH RP (remove nothere from the email address if mailing direct) "John Smith" wrote in message ... Thank you. How would I repeat this conditional format for a series of cells (D7 to D500)? -----Original Message----- Use conditional formatting with a formula of =ISNUMBER(MATCH($D$7,$A:$A,0)) and select a colour for the format. If that source sheet is a different sheet to D7, then you will need to give the cells in column A a workbook name and refer to that in the formula. -- HTH RP (remove nothere from the email address if mailing direct) "Fred Timmons" wrote in message ... I am managing a do-not-call list with a large contact database. How do I establish a poitive/negative match from source data. eg. Cell D7 value is: 212-555-1212. The source worksheet has a list of all phone numbers in Column A. So, if the value of Cell D7 matches a value in source worksheet Column A, I can conditionally format it. Thanks in advance. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
Copy conditional formatting across multiple rows? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
Formatting cells in Excel for certain Values to appear certain Col | Excel Worksheet Functions | |||
delete values in several cells without deleting the formulas | Excel Discussion (Misc queries) |