Aug 24 2020 06:28 AM
I need help setting a conditional format for my spreadsheet. My data goes from cell A1 thru I whatever depending on the size of my report each day. Row 1 is my header line.
Column F is where my criteria I want to zero in on is. It contains text such as Neurology Dept, Interventional Pain Dept, Ophthalmology Dept and so on. I have a good set of formulas to do what I want so far, but am having issues with one part.
I want to highlight the entire row when "NEUROLOGY" is found in Column F and to do that, I am using the following formula.
=SEARCH("NEUROLOGY", $F2)
I want to be able to use one formula to search for multiple words in Column F and highlight the row if it finds any of them, instead of using the above formula three different times for each word. I would like it to search for NEUROLOGY, INTERVENTIONAL and OPHTHALMOLOGY and apply the same formatting if it finds any of those words in Column F. I can't get this to work for the life of me!
Aug 24 2020 07:05 AM
Highlight all the cells down F1 you want to format. Click on Conditional formatting and select New Rule, Use Formula. put =OR(F1="Neurology",F1="Intervention",F1="Ophthalmology") then select Format, and choose your format. Say ok until you are out.
Aug 24 2020 07:15 AM
To make your conditional formatting more dynamic. You can type the words to search for in another column or sheet as shown in the attached.
Then use this formula:
=OR(ISNUMBER(SEARCH($I$2,$F2)),ISNUMBER(SEARCH($I$3,$F2)),ISNUMBER(SEARCH($I$4,$F2)))
Hope this is useful.
Cheers
Aug 24 2020 07:28 AM
@SqueakySneakers That won't work because the cells in column F contain more than just the word Neurology. For instance my column F will read, Neurology Danville D1 or Neurology Danville D2 and I want my formula to highlight the entire row any time it finds Neurology anywhere in the cell in column F.
Aug 24 2020 07:31 AM
Aug 24 2020 07:46 AM
Solution
If you prefer to "hardcode" the words in the formula, then this should do:
=OR(ISNUMBER(SEARCH("NEUROLOGY",$F2)),ISNUMBER(SEARCH("INTERVENTIONAL",$F2)),ISNUMBER(SEARCH("OPHTHALMOLOGY",$F2)))
Cheers
Aug 24 2020 10:13 AM
@wumolad That last code you provided is EXACTLY what I am looking for! After accidentally posting this exact question in a Google Sheets thread and it going on FOREVER and me trying different scenarios on my own, I FINALLY got what I want! Thank you SO MUCH!!!!
Aug 24 2020 11:27 AM
Aug 24 2020 07:46 AM
Solution
If you prefer to "hardcode" the words in the formula, then this should do:
=OR(ISNUMBER(SEARCH("NEUROLOGY",$F2)),ISNUMBER(SEARCH("INTERVENTIONAL",$F2)),ISNUMBER(SEARCH("OPHTHALMOLOGY",$F2)))
Cheers