Making #N/A invisible

[Guest]

I have been trying to get rid of some unsightly #N/A s on an Excel Spreadsheet until input is received. I had contemplated changing them to Zeros but I seem to be unable to get an IF statement to acknowledge them. I wonder is there an easy/smart way. Any help appreciated
Cheers
Sam

 

[Dave Hawley]

Hi

Not too sure if you just want to not see the #N/A or prevent it. If the former use Conditional formatting (under Format) and set the "Formula is" to: =ISNA($D$7) and then change the Font color and background color to the same.

If you are wanting prevent it here are a few options using the VLOOKUP:

=IF(COUNTIF(A1:A500,"MyData")=0,"",VLOOKUP("MyData",A1:C500,2,FALSE))

=IF(ISNA(VLOOKUP("MyData",A1:C500,2,FALSE)),"",VLOOKUP("MyData",A1:C500,2,FALSE))

The first method is the preferred one.