Referencing another Sheet for Conditional Format

[PlumGr8]

So i have cell range on another sheet, say D5 to F25 with data. Simple list on Serial Number (SN1001, SN1002, etc). Is there a way to reference these values from another sheet with conditional formatting to turn a cell yellow is there's a match? Like if Sheet 1 Cell D5 says "SN1001" and Sheet 2 has "SN1001" in the listed range it turns yellow. I know i can do it cell to cell, but i need to reference about 75 SN's and i keep getting a reference error. Any help would be great, thanks.

 

[Kaper]

Use a formula to create conditional formatting rule. The formula to be used is:

=COUNTIF(Sheet2!$D$5:$F$25;A2)>0

where:
Sheet2!$D$5:$F$25 is absolute (note dollars signs) range with SNs to be checked together with a sheetname (if sheet name contains sopace use apostrophes around it) [1 on screenshot]
A2 is a current cell relative (note no $s) address where content is to be checked and "painted" if found in reference range [2 on screenshot]

 

[PlumGr8]

Use a formula to create conditional formatting rule. The formula to be used is:

=COUNTIF(Sheet2!$D$5:$F$25;A2)>0

where:
Sheet2!$D$5:$F$25 is absolute (note dollars signs) range with SNs to be checked together with a sheetname (if sheet name contains sopace use apostrophes around it) [1 on screenshot]
A2 is a current cell relative (note no $s) address where content is to be checked and "painted" if found in reference range [2 on screenshot]

So here is what i have

=COUNTIF(Engine SN!$B$3:$K$35;D6)>0

But, i keep getting the syntax error. I've even tried changing the sheet name to Sheet2 but still get the error. Would it matter if Cell D6 is a merged block of a few cells? That cell is a merged cell of D6 - D10.

 

[Joe4]

If you have a space in your sheet name, you need to enclose the sheet name in single quotes, i.e.

=COUNTIF('Engine SN'!$B$3:$K$35;D6)>0

 

[PlumGr8]

If you have a space in your sheet name, you need to enclose the sheet name in single quotes, i.e.

=COUNTIF('Engine SN'!$B$3:$K$35;D6)>0

Hmm, i did try that, but still have that "There's a problem with this formula" warning.

 

[Joe4]

Where are you located?
What is used as the separator for your formula arguments? Is it a comma or semi-colon? It can differ based on your regional settings.

You are currently showing a semi-colon, but perhaps it should be a comma, i.e.

=COUNTIF('Engine SN'!$B$3:$K$35,D6)>0

 

[Kaper]

Semicolon is probably copied from my formula.
Sorry for that.
I expected you will replace it with your formula separator.
I usually change it manually to coma in my posts, but somehow I did not do that in post #2.

 

[Asbestos_Jen]

Conditional formatting doesn't work with references to other sheets in some versions of Excel. What version are you using?

 

[PlumGr8]

Where are you located?
What is used as the separator for your formula arguments? Is it a comma or semi-colon? It can differ based on your regional settings.

You are currently showing a semi-colon, but perhaps it should be a comma, i.e.

=COUNTIF('Engine SN'!$B$3:$K$35,D6)>0

That seemed to be it. I am in the US, i honestly didn't even know that ; and , made a difference like that between regions. But that seems to have worked, i got it to highlight for me. Appreciate the help very much.

 

[PlumGr8]

Conditional formatting doesn't work with references to other sheets in some versions of Excel. What version are you using?

Office 2019. I guess it was the difference between using ; and , I honestly had no idea that changed between regions.