Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have a spreadsheet with a list of products. Column D contains the
product names. I want to format the rows to stand out if a particular word is in the product name. I've got this to work: =FIND("Chef",$D1) What I'd really like to do is have the formula look for a variety of different products; e.g., "Chef", "Apple", "Lodge" ... etc. Is there any way to do that?? Thanks. Barb |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi!
FIND is case sensitive so Chef and chef do not match. Try this: =OR(ISNUMBER(FIND({"Chef","Apple","Lodge"},D1))) If you don't need it to be case sensitive replace FIND with SEARCH. Biff "The Moose" wrote in message oups.com... I have a spreadsheet with a list of products. Column D contains the product names. I want to format the rows to stand out if a particular word is in the product name. I've got this to work: =FIND("Chef",$D1) What I'd really like to do is have the formula look for a variety of different products; e.g., "Chef", "Apple", "Lodge" ... etc. Is there any way to do that?? Thanks. Barb |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I would put my list of words on another worksheet (hidden???) and give that
range a nice name--say myList. Then I'd use format|conditional formatting with a formula like: =MATCH(D1,myList,0) The Moose wrote: I have a spreadsheet with a list of products. Column D contains the product names. I want to format the rows to stand out if a particular word is in the product name. I've got this to work: =FIND("Chef",$D1) What I'd really like to do is have the formula look for a variety of different products; e.g., "Chef", "Apple", "Lodge" ... etc. Is there any way to do that?? Thanks. Barb -- Dave Peterson |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I don't think you can use arrays like this in conditional formatting.
Biff wrote: Hi! FIND is case sensitive so Chef and chef do not match. Try this: =OR(ISNUMBER(FIND({"Chef","Apple","Lodge"},D1))) If you don't need it to be case sensitive replace FIND with SEARCH. Biff "The Moose" wrote in message oups.com... I have a spreadsheet with a list of products. Column D contains the product names. I want to format the rows to stand out if a particular word is in the product name. I've got this to work: =FIND("Chef",$D1) What I'd really like to do is have the formula look for a variety of different products; e.g., "Chef", "Apple", "Lodge" ... etc. Is there any way to do that?? Thanks. Barb -- Dave Peterson |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Ooops!
Yep, you're right. No array constants. Biff "Dave Peterson" wrote in message ... I don't think you can use arrays like this in conditional formatting. Biff wrote: Hi! FIND is case sensitive so Chef and chef do not match. Try this: =OR(ISNUMBER(FIND({"Chef","Apple","Lodge"},D1))) If you don't need it to be case sensitive replace FIND with SEARCH. Biff "The Moose" wrote in message oups.com... I have a spreadsheet with a list of products. Column D contains the product names. I want to format the rows to stand out if a particular word is in the product name. I've got this to work: =FIND("Chef",$D1) What I'd really like to do is have the formula look for a variety of different products; e.g., "Chef", "Apple", "Lodge" ... etc. Is there any way to do that?? Thanks. Barb -- Dave Peterson |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
The way that I read the OP is that there is more than just a single word in
the cells. I could be wrong! Biff "Dave Peterson" wrote in message ... I would put my list of words on another worksheet (hidden???) and give that range a nice name--say myList. Then I'd use format|conditional formatting with a formula like: =MATCH(D1,myList,0) The Moose wrote: I have a spreadsheet with a list of products. Column D contains the product names. I want to format the rows to stand out if a particular word is in the product name. I've got this to work: =FIND("Chef",$D1) What I'd really like to do is have the formula look for a variety of different products; e.g., "Chef", "Apple", "Lodge" ... etc. Is there any way to do that?? Thanks. Barb -- Dave Peterson |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I try not to read the original question--just read the responses and then I try
to divine the original question from those answers. Maybe using a formula like: =SUM(COUNTIF(D1,"*"&MyList&"*")) would work. Still with that list of words on a separate sheet. Biff wrote: The way that I read the OP is that there is more than just a single word in the cells. I could be wrong! Biff "Dave Peterson" wrote in message ... I would put my list of words on another worksheet (hidden???) and give that range a nice name--say myList. Then I'd use format|conditional formatting with a formula like: =MATCH(D1,myList,0) The Moose wrote: I have a spreadsheet with a list of products. Column D contains the product names. I want to format the rows to stand out if a particular word is in the product name. I've got this to work: =FIND("Chef",$D1) What I'd really like to do is have the formula look for a variety of different products; e.g., "Chef", "Apple", "Lodge" ... etc. Is there any way to do that?? Thanks. Barb -- Dave Peterson -- Dave Peterson |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks, guy.
I, too, found out that the arrays didn't work. I haven't had a chance to try the other yet. I'm trying to do too many things at once -- keep getting sidetracked -- character flaw, I guess :GRIN: I WILL post back after trying the other suggestions to let you all know what worked. Thanks again. Barb Dave Peterson wrote: I try not to read the original question--just read the responses and then I try to divine the original question from those answers. Maybe using a formula like: =SUM(COUNTIF(D1,"*"&MyList&"*")) would work. Still with that list of words on a separate sheet. Biff wrote: The way that I read the OP is that there is more than just a single word in the cells. I could be wrong! Biff "Dave Peterson" wrote in message ... I would put my list of words on another worksheet (hidden???) and give that range a nice name--say myList. Then I'd use format|conditional formatting with a formula like: =MATCH(D1,myList,0) The Moose wrote: I have a spreadsheet with a list of products. Column D contains the product names. I want to format the rows to stand out if a particular word is in the product name. I've got this to work: =FIND("Chef",$D1) What I'd really like to do is have the formula look for a variety of different products; e.g., "Chef", "Apple", "Lodge" ... etc. Is there any way to do that?? Thanks. Barb -- Dave Peterson -- Dave Peterson |
#9
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thank you, thank you, thank you!!!
It WORKS :LOL: =SUM(COUNTIF(B1,"*"&mylist&"*")) -- highlights just the first colum. What I wanted was the whole row to highlight so that it REALLY stands out. A little tweaking of your formula, and I got EXACTLY what I wanted: =SUM(COUNTIF($B1,"*"&mylist&"*")) Thanks so much. Barb Dave Peterson wrote: I try not to read the original question--just read the responses and then I try to divine the original question from those answers. Maybe using a formula like: =SUM(COUNTIF(D1,"*"&MyList&"*")) would work. Still with that list of words on a separate sheet. Biff wrote: The way that I read the OP is that there is more than just a single word in the cells. I could be wrong! Biff "Dave Peterson" wrote in message ... I would put my list of words on another worksheet (hidden???) and give that range a nice name--say myList. Then I'd use format|conditional formatting with a formula like: =MATCH(D1,myList,0) The Moose wrote: I have a spreadsheet with a list of products. Column D contains the product names. I want to format the rows to stand out if a particular word is in the product name. I've got this to work: =FIND("Chef",$D1) What I'd really like to do is have the formula look for a variety of different products; e.g., "Chef", "Apple", "Lodge" ... etc. Is there any way to do that?? Thanks. Barb -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Conditional Formatting in Bar Charts with Labeling | Charts and Charting in Excel | |||
conditional formatting for cells containing words | Excel Discussion (Misc queries) | |||
enhanced conditional formatting | Excel Discussion (Misc queries) | |||
Copying conditional formatting...HELP PLEASE!!!! | Excel Worksheet Functions | |||
conditional formatting question | Excel Discussion (Misc queries) |