0

I have 2 sheets. Sheet1 & Sheet2.

Sheet 1 has a list of items with descriptions & specific numbers. Sheet 2 has a table filled with the same items.

What I am wanting to do, is highlight a selected cell in Sheet2 if a specific word is mentioned ANYWHERE in Sheet1 using conditional formatting.

For example, if my Sheet2 is a table of numbers 1-12 (Jan-Dec) 1 2 3 4 5 6 7 8 9 10 11 12

And I have a list of dates in Sheet1 column C ex.

January 1, 20xx February 10, 20xx January 20, 20xx December 18, 20xx July 10, 20xx March 5, 20xx September 9, 20xx

In Sheet2, I want the "1" cell highlighted if the word "January" is found ANYWHERE in Sheet1 (specifically in column C, but if there is a command for the whole sheet that would be useful as well). As long as it appears ONCE, I want the cell with "1" highlighted. And so on.

I have gotten a formula to where I can highlight the cell "1" in Sheet2 if I point it to only ONE cell

=SEARCH("January",Sheet2!C5)

but I can't figure out how to make it search the entire page, a certain range, or even just that column. I have tried adjusting the C5 to C5:C13, C:C, A1:C9, $C$5, and so many other combinations. I just want it to comb a specific range and highlight my cell as long as the word is found even once.

Thank you!

0

You need to use this as New Rule to format selected range in Sheet 2:

=NOT(ISNA(VLOOKUP(D$15,Sheet1!$C:$C,1,FALSE)))

Note,

  • To make the Search Dynamic in Cell D15write January.
  • This formula will search January in entire C column of Sheet 1 & Format the Selected Data range in Sheet 2since it returns TRUE.

Adjust the cell references in formula as needed.

  • The formula kind of worked. I change the "D$15" to January right? So my column C in Sheet1 has more words than just the dates. Some of them may say "Between January 1 & January 10." I played with the formula to make sure it worked, and it looks like it may only be reading the first word of each cell in the column? If I change the word January to another month, it still works. But when I change it to let's say just the number 10 I do not get the conditional formatting I am looking for, as if it doesn't find the 10 in a string of text containing it even though it is in column C. – John Aug 21 '18 at 0:25
  • @John, if column C has Date rather than Text January then you need to apply MMMM format to the Column C then Excel will convert those dates to respective months and this Formula will read the January for conditional formating. – Rajesh S Aug 21 '18 at 5:17
  • ,,,Cont. if then you find any issue then better share the Sample data with us will help us to fix it. ☺ – Rajesh S Aug 21 '18 at 5:18

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.