Google Sheets - Search and Highlight Cells with the Matching Data

Question

I'm trying to make something like this: https://productivityspot.com/search-in-google-sheets/

But in my case, "search cell" and cells that need to be highlighted are in the different sheets. And I need to be able to search for multiple strings, not just one.

So, here is the exact problem:

• The table is located in a sheet called "Tabela"

• City names are in cells C4, C5, C6, etc.

• I need to type city names in another sheet called "Filter" in cells AP5, AP6, AP7, etc., and in cells AQ5, AQ6, AQ7, etc.

• When in sheet "Filter" I write "Tuzla" in cell AP5 and "Lukavac" in cell AP6, I need every "Tuzla" and every "Lukavac" in the sheet "Tabela" in C4 and below to be highlighted with red. And when I do that in cells AQ5, AQ6, etc., I need them highlighted in green.

• So, cities entered in AP column should be highlighted red, and cities entered in AQ should be highlighted green. And if the same city is entered in both the AP and AQ column, some kind of warning would be nice.

question from:https://stackoverflow.com/questions/66068478/google-sheets-search-and-highlight-cells-with-the-matching-data

Answer 1

You can use INDIRECT function in your conditional formatting custom formulas to highlight cells based on values from different sheet:

In your sample, you would need three rules:

Highlight Cells Red:

Custom Formula: =COUNTIF(INDIRECT("Filter!$AP5:$AP"), $C4)>0

Highlight Cells Green:

Custom Formula: =COUNTIF(INDIRECT("Filter!$AQ5:$AQ"), $C4)>0

For the warning, you can turn the cells a different color (I'll use gray). This rule should be on the top.

Custom Formula: =COUNTIF(INDIRECT("Filter!$AP5:$AQ"), $C4)>0

Sample Sheet:

References:

INDIRECT()

Conditional Formatting from another sheet