Conditional formatting to identify a list with errors

[ollyhughes1982]

Hi,

I have an issue with conditional formatting in a cell. I want cell Q2 to turn orange (and font to turn black) if the range Q3:Q10002 contains any errors. I have used =ISERROR($Q$3:$Q$10002) and applied it to $Q$2, but this isn’t working for some reason. Where am I going wrong with this, please?

Link to OneDrive file: parkrun - Combined Events (Venues) - For Dump WORKING.xlsx

Thanks in advance!

Olly.

 

[shinigamilight]

=SUMPRODUCT(--ISERROR($Q$3:$Q$10002))>0

try this

 

[ollyhughes1982]

=SUMPRODUCT(--ISERROR($Q$3:$Q$10002))>0

try this

Thanks, that worked great!

 

[ollyhughes1982]

Similarly, In R2 I am using I am using =COUNTIF($R$3:$R$10002,"No") to identify if any cells contain 'No', but I have now realised that I also need to cater for errors too. Would there be a way to add something in to that formula, so that R2 highlights if R3:R10002 contains 'No' OR an error?

 

[shinigamilight]

Thanks, that worked great!

I'm glad that it worked out. Have a nice weekend.

 

[shinigamilight]

Similarly, In R2 I am using I am using =COUNTIF($R$3:$R$10002,"No") to identify if any cells contain 'No', but I have now realised that I also need to cater for errors too. Would there be a way to add something in to that formula, so that R2 highlights if R3:R10002 contains 'No' OR an error?

=OR(COUNTIF($R$3:$R$10002,"No"),SUMPRODUCT(--ISERROR($R$3:$R$10002))>0)

try this one

 

[ollyhughes1982]

=OR(COUNTIF($R$3:$R$10002,"No"),SUMPRODUCT(--ISERROR($R$3:$R$10002))>0)

try this one

Yes, that works perfectly. Thanks so much!