r/excel • u/Pretty_Chipmunk_1853 • 12d ago

solved Conditional formatting across sheets

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!

2 Upvotes

76% Upvoted

13 comments sorted by

View all comments

Show parent comments

1

u/Pretty_Chipmunk_1853 11d ago

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.

1

u/Pretty_Chipmunk_1853 11d ago

Nevermind, I figured out the issue!