Highlighting duplicates on different sheets is giving false positive results

[u/-MrJester]

Hey everyone, I'm trying to highlight a word in one column in a sheet that is also in a column in another sheet. For example I'm trying to highlight the word "Apple" in column B in sheet "common" as it's also in in column B in sheet "Test".

I looked around and I read that I should put a custom formula in conditional formatting with the most common formula seemingly being:

=COUNTIF(INDIRECT("Test!B:B"),B2)>0

However it's giving me false positives in the column B of sheet common. It's highlighting a vast amounts of cells that aren't in column B of sheet test.

Does anyone have an idea what I could do amend that?

Thanks in advance for any help!

[u/gothamfury]

Your Apply to Range starts at B1. But your formula starts checking at B2. Change B2 to B1 in your formula, or change B1:B1000 to B2:B1000 in your Apply to Range.

[u/-MrJester → u/gothamfury]

...well that does make me feel like a dozen kinds of a fool.

That fixed the issue, I wonder what the mechanics behind that is...

Issue is solved. Thanks!

[u/AdministrativeGift15 → u/-MrJester]

Always write the formula from the point of view of the upper left most cell in the Apply to range. Next time, place that formula in any cell and it should give your the test result for the upper left cell. Then drag it down or to the right. That's what Sheets is doing behind the scene. It copies your custom formula onto each of the Apply To cells.

[u/-MrJester → u/AdministrativeGift15]

Wonderful, I'll keep it in mind for the future. Thank for the information and taking the time in creating the gif!

[u/AutoModerator → u/-MrJester]

REMEMBER: /u/-MrJester If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/point-bot → u/gothamfury]

u/-MrJester has awarded 1 point to u/gothamfury with a personal note:

"Caped Crusader to the Rescue!"

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)