Trouble correctly identifying duplicates across worksheets

[jrryan]

Hi, I'm trying to use a text field to exactly match duplicate data from one worksheet where the data is stored horizontally with another where the data is stored vertically using conditional formatting.

I have used the COUNTIF method, MATCH, XMATCH ... Nothing works. Conditional formatting recognises it should be finding something, but it highlights the wrong fields, including items that aren't even in the second list.

Here's the formula entered into conditional formatting on the range 4:4 in sheet 1 that should work, but doesn't: "=countif('sheet 2'A:A, G4)"

What is returned is a sequence of red shaded cells that do not correlate with the data in sheet 2. It seems random, but it is the same shaded cells however I approach it. Some values are in sheet 2, some arent. I just want to see what isn't in sheet 2 so that I can add it without checking each and every one between sheets.

[MarvinP]

Hi jrryan,

I think you need to put some dollar signs in your formula. See the attached that uses random numbers in two sheets. Press F9 a few times to recalculate and see what matches. Hope this example helps.
Countif Rows vs Cols.xlsx
If I remember correctly, Conditional Formatting doesn't work on multiple sheets. It only works on a single sheet.

[jrryan]

Hi, you can use conditional formatting across worksheets, (or are supposed to be able to) by using a formula that references the worksheets in the classic mode. I have done this with the example workbook you provided and it demonstrates the same issue I am experiencing. See attached.

[MarvinP]

Hi jrryan,

Good Job!! You taught the old guy something. It looks like CF across multiple sheets now works in Excel 2010 and beyond. See:
https://contexturesblog.com/archives...fferent-sheet/

Did my answer solve your problem? Did putting the Absolute Reference (the dollar signs) in the formula solve your problem??

[jrryan]

Did my answer solve your problem? Did putting the Absolute Reference (the dollar signs) in the formula solve your problem??

Sadly, no. I had already tried TBF. If you take a look at my attachment, you'll see I used your formula in the CF.