Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference Formula
I need a way to find cells that contain information that is included in
another cell. Example: A B 1 John Smith Smith, John 2 Jane Doe ABC Company 3 Billy Jones Jones, Billy I'd like to use conditional formatting to highlight cells B1 and B3 because it contains a word that is also in cells A1 and A3 respectively. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference Formula
User wrote:
I need a way to find cells that contain information that is included in another cell. Example: A B 1 John Smith Smith, John 2 Jane Doe ABC Company 3 Billy Jones Jones, Billy I'd like to use conditional formatting to highlight cells B1 and B3 because it contains a word that is also in cells A1 and A3 respectively. Thanks Assuming there are exactly two words to check from column A and they will be separated by exactly one space, select B1:B3 and use this formula in conditional formatting: =OR(ISNUMBER(SEARCH(LEFT($A1,SEARCH (" ",$A1)-1),$B1)),ISNUMBER(SEARCH(MID($A1,SEARCH(" ",$A1)+1,99),$B1))) (N.B. I had to enter a hard line break for this post to make it clear a space is between the quotes.) If you meant "contains /both/ words" change the OR to AND. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference Formula
I had been waiting for a while for others to give you a better solution.
One way is to insert a couple of columns and split the names for each column into two by using the <data<text to columns procedure. After that you can create conditional formatting using the is formula option and using the formula =OR(C1=B1,C1=A1) for C1 and similar for D1 =OR(D1=B1,D1=A1) copy the formal down using the format painter. -- Greetings from New Zealand "User" wrote in message ... I need a way to find cells that contain information that is included in another cell. Example: A B 1 John Smith Smith, John 2 Jane Doe ABC Company 3 Billy Jones Jones, Billy I'd like to use conditional formatting to highlight cells B1 and B3 because it contains a word that is also in cells A1 and A3 respectively. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set a formula, that add next cell reference in a Formula | Excel Worksheet Functions | |||
Formula to reference every second row | Excel Discussion (Misc queries) | |||
Reference formula | Excel Discussion (Misc queries) | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Is it Possible in a formula to have a formula for cell reference? | Excel Worksheet Functions |