Can I hide a cell currently showing #N/A ?

[Guest]

I have a Excel 2000 spreadsheet which does a vlookup to other spreadsheets.
Because some of the cells attempt to do a vlookup on cell which are currently
empty I get a #N/A. I dont mind but the users are complaining. Can I easily
hide these cells when #N/A is shown?

 

[Jim Rech]

You can use a formula like this when you want nothing showing if the lookup
cell is empty:

=IF(A1=0,"",VLOOKUP(A1,C1:C10,FALSE))

--
Jim Rech
Excel MVP
|I have a Excel 2000 spreadsheet which does a vlookup to other spreadsheets.
| Because some of the cells attempt to do a vlookup on cell which are
currently
| empty I get a #N/A. I dont mind but the users are complaining. Can I
easily
| hide these cells when #N/A is shown?

 

[Frank Kabel]

Hi
one way:
=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

 

[Andy]

What about using Conditional Formatting, if the cell contents are
#N/A, the format is white on white? It would be easier to apply this
to a block of cells than it would be to rewrite the formulas, unless
you needed those cells to be blank because other cell calculations
depended on them (doesn't seem to be the case here). Or is there
another reason this would be a bad approach?

 

[Jim Rech]

Lots of ways to peel a banana. Besides the possible problem you cite, if
you print "black and white" the N/A would appear in the printout.

--
Jim Rech
Excel MVP
| What about using Conditional Formatting, if the cell contents are
| #N/A, the format is white on white? It would be easier to apply this
| to a block of cells than it would be to rewrite the formulas, unless
| you needed those cells to be blank because other cell calculations
| depended on them (doesn't seem to be the case here). Or is there
| another reason this would be a bad approach?
|
|
|

 

[Gord Dibben]

If you go Frank's route, which I think you should, and if you have many
formulas to change you could use this macro.

Sub NATrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISNA*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISNA(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub

Gord Dibben Excel MVP