How to apply conditional formatting to highlight cells using cross-sheet formulas in Google Sheets

Other Questions Google Sheets
1

I’m trying to set up conditional formatting in Google Sheets but can’t get the formula syntax right.

Here’s my setup:

  • First sheet: Has column A with numbers 1 through 100
  • Second sheet: Contains various text values in column A, including repeated instances of “target”

I want to highlight a cell in the first sheet based on how many times “target” appears in the second sheet. For example, if “target” appears 5 times, then cell A5 in the first sheet should be highlighted red.

I’ve tried using custom formulas with COUNTIF and also tried creating a named range, but nothing seems to work properly.

First Sheet:

| Number |
|   1    |
|   2    |
|   3    |
|   4    |
|   5    |

Second Sheet:

| Values  |
| target  |
| target  |
| other   |
| data    |
| target  |

In this example, since “target” appears 3 times, row 3 in the first sheet should be highlighted. When I add another “target” to the second sheet, the highlighting should automatically move to row 4.

What’s the correct conditional formatting formula to make this work?

2

I had a similar issue and discovered the problem was likely with sheet naming conventions. The formula =ROW()=COUNTIF(Sheet2!A:A,"target") should work, but make sure your second sheet is actually named “Sheet2” or adjust the reference accordingly. If your sheet has spaces in the name, you need single quotes like 'My Sheet'!A:A. Also worth checking if there are any trailing spaces in your “target” text values since COUNTIF is case-sensitive and space-sensitive. I recommend testing the COUNTIF formula separately in a cell first to verify it returns the expected count before applying it to conditional formatting. Another common mistake is selecting the wrong range when setting up the conditional formatting rule.

3

try this formula instead: =ROW()=COUNTIF(Sheet2!A:A,"target") - this will highlight only the exact row that matches the count. so if target appears 5 times, only row 5 gets highlighted instead of rows 1-5. make sure to apply it to your range A1:A100 in conditional formating

4

To apply conditional formatting correctly, you should use a formula that compares each row based on the count of ‘target’ in the second sheet. First, select the range in your first sheet (for example, A1:A100), then navigate to Format > Conditional formatting. Choose ‘Custom formula is’ and input this formula:

=ROW(A1)<=COUNTIF(Sheet2!A:A, "target")

This will highlight the rows in the first sheet where the row number is less than or equal to the count of ‘target’ entries in your second sheet. This way, if ‘target’ appears three times, it will highlight the first three rows of your selected range. Ensure there are no discrepancies or extra spaces in your second sheet that could affect the COUNTIF function.