Conditional formatting for error values

JanSte

Hi,

I'm using VLOOKUP for status information from another table. The referenced table sometimes does not have the requested row, so I get back the #NO MATCH error. This does not looks nice, so I was trying to hide from the users and set conditional formatting - if column value equals to #NO MATCH set the font foreground color to white -> the error will be there, but invisible.

It looks like this does not work -the error messages are not possible to format?

Andrée Starå

@JanSte

Happy to help!

I'd recommend using the IFERROR instead of the Conditional Formatting.

Make sense?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @JanSte

I hope you're well and safe!

I'd recommend adding IFERROR to the formula instead.

https://help.smartsheet.com/function/iferror

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

JanSte

hi @Andrée Starå ,

this is conditional formatting, so I can't use IFERROR :(

If you go to the conditional formatting dialogue you can Add new rule and then Set condition and finally Select criteria. In my case I select column and then select value from the list (here I pick the #NO MATCH) and then pick the appropriate column formatting. This works on any values but not on the #NO MATCH. Probably also not on other errors...

The question is, if this is expected behavior? And if yes, are there any other ways how to format cells with errors?

Thanks!

Jan

Andrée Starå

@JanSte

Happy to help!

I'd recommend using the IFERROR instead of the Conditional Formatting.

Make sense?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

JanSte

Hi @Andrée Starå ,

Now I understand. Simply using the IFERROR put there another value which can be conditional formatted.

Thx, this works!

Andrée Starå

@JanSte

Excellent!

You're more than welcome!

You can also just add "" as the result of the IFERROR, and then the cell will stay empty so that you wouldn't need the Conditional Formatting.