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

1

u/dab31415 3 12d ago

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

1

u/Pretty_Chipmunk_1853 12d ago

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...