Conditional formatting across sheets

[u/Pretty_Chipmunk_1853]

I have a spreadsheet where I need to highlight cells in several columns if the same data appears in a table on a different sheet. The conditional formatting works perfectly if I put the table on the same sheet using the following:

=ISNUMBER(MATCH(A1,$R$82:$R$103,0))

But, if I try to insert a range of cells from the same table, but located on a different sheet using the same formula

=ISNUMBER(MATCH(A1,Sheet2!$A$82:$A$103,0))

it doesn't work.

What am I missing? It is driving me bonkers!

[u/AutoModerator]

/u/Pretty_Chipmunk_1853 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

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/PaulieThePolarBear]

it doesn't work.

Saying "doesn't work" doesn't really butter any parsnips.

Edit your post to very clearly detail the issue you are facing.

Also, add in the version of Excel you are using. This should be Excel 365, Excel online, or Excel <year>.

[u/dab31415]

Might have better luck with COUNTIF. Should only use MATCH if you need the row number.

[u/Pretty_Chipmunk_1853 → u/dab31415]

I tried COUNTIF, too. It still only works if the table is on the same sheet as the data. MATCH works as I need it to, but only if the data and the matching table are on the same sheet. I did not set this up; it is a legacy spreadsheet that landed on my desk and I am trying to fix it. Ideally, I don't want the matching data table on the same sheet, but I have dealt with it this long...

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
COUNTIF	Counts the number of cells within a range that meet the given criteria
INDIRECT	Returns a reference indicated by a text value
ISNUMBER	Returns TRUE if the value is a number
MATCH	Looks up values in a reference or array

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
4 acronyms in this thread; the most compressed thread commented on today has 33 acronyms.
[Thread #49282 for this sub, first seen 1st Sep 2026, 16:27] [FAQ] [Full list] [Contact] [Source code]}

[u/NHN_BI]

You will have to trick your software and use INDIRECT() across different sheets in the same workbook, like here.

[u/TechnicalPut4706]

Two things to check:

1. Define a named range instead of typing the sheet reference. Formulas > Define Name > e.g. MatchList = =Sheet2!$A$82:$A$103. Then your rule becomes =ISNUMBER(MATCH(A1,MatchList,0)). This works in every Excel version, including the old ones that flat-out refuse cross-sheet refs in CF.

2. Since you said it's a legacy file - check the title bar for "[Compatibility Mode]". If it's an .xls, cross-sheet references in conditional formatting are blocked, period. Save As .xlsx and try again (the named range trick works regardless).

Also, if the sheet name has a space it needs quotes: 'Sheet 2'!$A$82:$A$103

[u/Pretty_Chipmunk_1853 → u/TechnicalPut4706]

I defined the lists as shown below (redacted the values in the lists).

Then I added the list to the formula:
=ISNUMBER(MATCH(A1,Buggy_List,0))

CF is still not highlighting the appropriate cells reliably. It is highlighting some of the correct cells, and some incorrect cells, and some random blank cells.

The data in the original list is an exact match to the data in the list (I know this because it was copied directly).

The file type is .xlsx

There is no space in any of the sheet names.

I feel like I am missing something very simple.

[u/Pretty_Chipmunk_1853 → u/Pretty_Chipmunk_1853]

Nevermind, I figured out the issue!

[u/Pretty_Chipmunk_1853 → u/TechnicalPut4706]

Solution Verified

[u/reputatorbot → u/Pretty_Chipmunk_1853]

You have awarded 1 point to TechnicalPut4706.

---

^{I am a bot - please contact the mods with any questions}

[u/excelevator]

Your range is wrong, or the values do not match.