MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Hidding #N/A, #Value!, ect... Forgot How...


Posted by Tom Woodyard on May 23, 2001 10:57 AM

I used to know a Tip I thought I got from this site but I can't find it. I saw a tip that showed how to hide these error messages in cell until a value was placed in the cell it is refrenceing. Anyone remember how to do this?

twoodyard@cadet.com


Posted by Barrie Davidson on May 23, 2001 11:03 AM

If you are looking to hide all error values, use an if statement. Something like:
=IF(ISERROR("Condition"),"","Value to Return"). If you need any more help let me know.

Regards,
Barrie

Posted by IML on May 23, 2001 11:08 AM

If you're look up is dependant on a cell that is to be filled in later, you could also use an if statement. Like if your user is entering something in A1

=IF(A1<>"",lookup(A1,range,col1,false),"")

Barry's is probably better, but this is just the way I normally do it.

good luck

Posted by Aladin Akyurek on May 23, 2001 11:09 AM

Tom,

It's not a good idea to "suppress" every type of error you can get. I wouldn't recommend hiding/suppressing errors like #DIV/0!, #VALUE!, #NUM!, etc. because these indicate serious problems in the processing involved.

Having said that, you can make use of ISERROR that catches any error:

=IF(ISERROR(f),"",f) where f is the actual formula that does a computation.

If you want to suppress just #N/A which can result from lookup formulas, you can use

=IF(ISNA(lookup-f),"",lookup-f) where lookup-f is the actual lookup formula that you use.

Aladin

Posted by Tom Woodyard on May 23, 2001 11:14 AM

Got it. Thank you very much!

Posted by Tom Woodyard on May 23, 2001 11:16 AM