Conditional formatting for error values
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?
Best Answer
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
Hi @JanSte
I hope you're well and safe!
I'd recommend adding IFERROR to the formula instead.
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi @Andrée Starå ,
Now I understand. Simply using the IFERROR put there another value which can be conditional formatted.
Thx, this works!
-
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.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives