Ignoring #n/a

T

The Toasterman

A spreadsheet I am working on return #n/a into a cell as it only dins a blank
in the validation menu we are using.

Is there any way to get excel to ignore #n/a & show a blank?
 
C

CLR

The #N/A error is usually the result of a formula..........try wrapping that
formula in an IF statement, like......

=IF(ISNA(YourFormula),"",YourFormula)

Vaya con Dios,
Chuck, CABGx3
 
T

The Toasterman

Nope, doesn't like that. The formula in the cell is a lookup

=if(isna(=LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)),"",(=LOOKUP($H13,$H$48:$I$98,$G$48:$G$98))

Doesn't want to close the formula.

???
 
C

CLR

Try it this way..........

=if(isna(LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)),"",(LOOKUP($H13,$H$48:$I$98,$G$48:$G$98))


Vaya con Dios,
Chuck, CABGx3
 
J

JLatham

Needs to be rewritten a
=IF(ISNA(LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)),"",LOOKUP($H13,$H$48:$I$98,$G$48:$G$98))

Note where pairs of parenthesis along with = symbols disappeared within the
formula when written as an IF statement.
 
T

The Toasterman

Thanks guys, works well.

Cheers

JLatham said:
Needs to be rewritten as
=IF(ISNA(LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)),"",LOOKUP($H13,$H$48:$I$98,$G$48:$G$98))

Note where pairs of parenthesis along with = symbols disappeared within the
formula when written as an IF statement.
Click to expand...
 
G

Gord Dibben

If you have a great whack of these, maybe this macro will help.

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 MS Excel MVP
 
Top