| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Contains the Word(s)
Is there a conditional formatting formula that can format an entire row a
certain way if Column A or Column B contain all or one of the words in a list. Basically if either Column A or Column B contain "dog" and/or "cat" and/or "mouse" and/or .... then I want the whole row to be flagged/shaded/etc. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Contains the Word(s)
Put the words in a list, say M1:M10, and use a formula of
=OR(AND($A2<"",SUMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$A2))))),AND($B2<"",S UMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$B2)))))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Keep It Simple Stupid" wrote in message ... Is there a conditional formatting formula that can format an entire row a certain way if Column A or Column B contain all or one of the words in a list. Basically if either Column A or Column B contain "dog" and/or "cat" and/or "mouse" and/or .... then I want the whole row to be flagged/shaded/etc. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Contains the Word(s)
Using the same setup as Bob, I'd use the formula
=SUM(ISNUMBER(SEARCH($M1:$M$10,$A2&" "&$B2))*($M$1:$M$10<"")) "Bob Phillips" wrote: Put the words in a list, say M1:M10, and use a formula of =OR(AND($A2<"",SUMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$A2))))),AND($B2<"",S UMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$B2)))))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Keep It Simple Stupid" wrote in message ... Is there a conditional formatting formula that can format an entire row a certain way if Column A or Column B contain all or one of the words in a list. Basically if either Column A or Column B contain "dog" and/or "cat" and/or "mouse" and/or .... then I want the whole row to be flagged/shaded/etc. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Contains the Word(s)
The formula ended up highlighting everything, whether the words were located
in the column or not. Are you sure this would be the formula to format Row X if Row X has Dog, Cat, etc in column A or B? What could I have done wrong???? (So close, yet so far!) "Bob Phillips" wrote: Put the words in a list, say M1:M10, and use a formula of =OR(AND($A2<"",SUMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$A2))))),AND($B2<"",S UMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$B2)))))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Keep It Simple Stupid" wrote in message ... Is there a conditional formatting formula that can format an entire row a certain way if Column A or Column B contain all or one of the words in a list. Basically if either Column A or Column B contain "dog" and/or "cat" and/or "mouse" and/or .... then I want the whole row to be flagged/shaded/etc. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Contains the Word(s)
daddylonglegs wrote...
Using the same setup as Bob, I'd use the formula =SUM(ISNUMBER(SEARCH($M1:$M$10,$A2&" "&$B2))*($M$1:$M$10<"")) "Bob Phillips" wrote: Put the words in a list, say M1:M10, and use a formula of =OR(AND($A2<"",SUMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$A2))))), AND($B2<"",SUMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$B2)))))) .... Also using Bob's setup, =SUM(COUNTIF($A2:$B2,"*"&$M$1:$M$10&"*")) to match words in M1:M10 as substrings in A2:B2 or =SUM(COUNTIF($A2:$B2,$M$1:$M$10)) to match words in M1:M10 as entire contents of cells in A2:B2. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Contains the Word(s)
Yes, I am absolutely sure. As given by me, the first row selected should be
row 2, if yours is different, change the A2 and B2. But, Harlan gave a much better formula than mine (and I can vouch for that one as well). -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Keep It Simple Stupid" wrote in message ... The formula ended up highlighting everything, whether the words were located in the column or not. Are you sure this would be the formula to format Row X if Row X has Dog, Cat, etc in column A or B? What could I have done wrong???? (So close, yet so far!) "Bob Phillips" wrote: Put the words in a list, say M1:M10, and use a formula of =OR(AND($A2<"",SUMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$A2))))),AND($B2<"",S UMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$B2)))))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Keep It Simple Stupid" wrote in message ... Is there a conditional formatting formula that can format an entire row a certain way if Column A or Column B contain all or one of the words in a list. Basically if either Column A or Column B contain "dog" and/or "cat" and/or "mouse" and/or .... then I want the whole row to be flagged/shaded/etc. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Contains the Word(s)
SUCCESS! Yesterday was too long of a day. A $ was missing from one of the
formulas. I ended up using =SUM(ISNUMBER(SEARCH($M$1:$M$10,$A2&" "&$B2))*($M$1:$M$10<"")) ....and it works BEAUTIFULLY! THANKS SO MUCH FOR YOUR HELP, HARLAN AND BOB! "Bob Phillips" wrote: Yes, I am absolutely sure. As given by me, the first row selected should be row 2, if yours is different, change the A2 and B2. But, Harlan gave a much better formula than mine (and I can vouch for that one as well). -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Keep It Simple Stupid" wrote in message ... The formula ended up highlighting everything, whether the words were located in the column or not. Are you sure this would be the formula to format Row X if Row X has Dog, Cat, etc in column A or B? What could I have done wrong???? (So close, yet so far!) "Bob Phillips" wrote: Put the words in a list, say M1:M10, and use a formula of =OR(AND($A2<"",SUMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$A2))))),AND($B2<"",S UMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$B2)))))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Keep It Simple Stupid" wrote in message ... Is there a conditional formatting formula that can format an entire row a certain way if Column A or Column B contain all or one of the words in a list. Basically if either Column A or Column B contain "dog" and/or "cat" and/or "mouse" and/or .... then I want the whole row to be flagged/shaded/etc. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Contains the Word(s)
Harlan's was better than that one
=SUM(COUNTIF($A2:$B2,"*"&$M$1:$M$10&"*")) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Keep It Simple Stupid" wrote in message ... SUCCESS! Yesterday was too long of a day. A $ was missing from one of the formulas. I ended up using =SUM(ISNUMBER(SEARCH($M$1:$M$10,$A2&" "&$B2))*($M$1:$M$10<"")) ...and it works BEAUTIFULLY! THANKS SO MUCH FOR YOUR HELP, HARLAN AND BOB! "Bob Phillips" wrote: Yes, I am absolutely sure. As given by me, the first row selected should be row 2, if yours is different, change the A2 and B2. But, Harlan gave a much better formula than mine (and I can vouch for that one as well). -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Keep It Simple Stupid" wrote in message ... The formula ended up highlighting everything, whether the words were located in the column or not. Are you sure this would be the formula to format Row X if Row X has Dog, Cat, etc in column A or B? What could I have done wrong???? (So close, yet so far!) "Bob Phillips" wrote: Put the words in a list, say M1:M10, and use a formula of =OR(AND($A2<"",SUMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$A2))))),AND($B2<"",S UMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$B2)))))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Keep It Simple Stupid" wrote in message ... Is there a conditional formatting formula that can format an entire row a certain way if Column A or Column B contain all or one of the words in a list. Basically if either Column A or Column B contain "dog" and/or "cat" and/or "mouse" and/or .... then I want the whole row to be flagged/shaded/etc. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| All words and Symbols of keybord should be added to Auto Shapes | Excel Discussion (Misc queries) | |||
| Combining Words | Excel Worksheet Functions | |||
| Can excel underline misspelled words like Word? | Excel Discussion (Misc queries) | |||
| triadic combinations of words | Excel Worksheet Functions | |||
| Spell Checking - Special Words Not Picked Up by Excel | Excel Discussion (Misc queries) | |||
Linear Mode
