how to hide #N/A's?

[Todd]

I am using a formula that returns #N/A frequently and I am wondering if
there is a way using conditional formatting or a different format feature to
not have those returns show? All the #N/A make the worksheet look cluttered.

TIA


Todd

 

[Don Guillett]

The best way to do this is
=if(isna(myformula),"",myformula)

 

[F6Hawk]

I used this formula for that issue:

=IF(A20="","",IF(ISNA(INDEX(AP19:AR800,MATCH(A20,AP19:AP800,0),2)),"",INDEX(AP19:AR800,MATCH(A20,AP19:AP800,0),2)))

Mine involves looking data up in a table, but I think the ISNA() function is
what you are looking for. Do a search for IS functions, and you will see all
of them.

Perhaps what you are looking for is something along these lines:
=IF(ISNA(your.formula), "", your.formula)

HTH!
Dave

 

[F6Hawk]

I used this formula for that issue:

=IF(A20="","",IF(ISNA(INDEX(AP19:AR800,MATCH(A20,AP19:AP800,0),2)),"",INDEX(AP19:AR800,MATCH(A20,AP19:AP800,0),2)))

Mine involves looking data up in a table, but I think the ISNA() function is
what you are looking for. Do a search for IS functions, and you will see all
of them.

Perhaps what you are looking for is something along these lines:
=IF(ISNA(your.formula), "", your.formula)

HTH!
Dave

 

[F6Hawk]

I used this formula for that issue:

=IF(A20="","",IF(ISNA(INDEX(AP19:AR800,MATCH(A20,AP19:AP800,0),2)),"",INDEX(AP19:AR800,MATCH(A20,AP19:AP800,0),2)))

Mine involves looking data up in a table, but I think the ISNA() function is
what you are looking for. Do a search for IS functions, and you will see all
of them.

Perhaps what you are looking for is something along these lines:
=IF(ISNA(your.formula), "", your.formula)

HTH!
Dave

 

[F6Hawk]

I used this formula for that issue:

=IF(A20="","",IF(ISNA(INDEX(AP19:AR800,MATCH(A20,AP19:AP800,0),2)),"",INDEX(AP19:AR800,MATCH(A20,AP19:AP800,0),2)))

Mine involves looking data up in a table, but I think the ISNA() function is
what you are looking for. Do a search for IS functions, and you will see all
of them.

Perhaps what you are looking for is something along these lines:
=IF(ISNA(your.formula), "", your.formula)

HTH!
Dave

 

[F6Hawk]

I used this formula for that issue:

=IF(A20="","",IF(ISNA(INDEX(AP19:AR800,MATCH(A20,AP19:AP800,0),2)),"",INDEX(AP19:AR800,MATCH(A20,AP19:AP800,0),2)))

Mine involves looking data up in a table, but I think the ISNA() function is
what you are looking for. Do a search for IS functions, and you will see all
of them.

Perhaps what you are looking for is something along these lines:
=IF(ISNA(your.formula), "", your.formula)

HTH!
Dave

 

[F6Hawk]

I used this formula for that issue:

=IF(A20="","",IF(ISNA(INDEX(AP19:AR800,MATCH(A20,AP19:AP800,0),2)),"",INDEX(AP19:AR800,MATCH(A20,AP19:AP800,0),2)))

Mine involves looking data up in a table, but I think the ISNA() function is
what you are looking for. Do a search for IS functions, and you will see all
of them.

Perhaps what you are looking for is something along these lines:
=IF(ISNA(your.formula), "", your.formula)

HTH!
Dave

 

[F6Hawk]

I used this formula for that issue:

=IF(A20="","",IF(ISNA(INDEX(AP19:AR800,MATCH(A20,AP19:AP800,0),2)),"",INDEX(AP19:AR800,MATCH(A20,AP19:AP800,0),2)))

Mine involves looking data up in a table, but I think the ISNA() function is
what you are looking for. Do a search for IS functions, and you will see all
of them.

Perhaps what you are looking for is something along these lines:
=IF(ISNA(your.formula), "", your.formula)

HTH!
Dave

 

[F6Hawk]

I used this formula for that issue:

=IF(A20="","",IF(ISNA(INDEX(AP19:AR800,MATCH(A20,AP19:AP800,0),2)),"",INDEX(AP19:AR800,MATCH(A20,AP19:AP800,0),2)))

Mine involves looking data up in a table, but I think the ISNA() function is
what you are looking for. Do a search for IS functions, and you will see all
of them.

Perhaps what you are looking for is something along these lines:
=IF(ISNA(your.formula), "", your.formula)

HTH!
Dave

 

[F6Hawk]

Sorry about the multiples, I am using the M$ browser interface, and it kept
telling me my post did not get sent, so I kept trying.

Now I know better.

 

[Domenic]

Try the following...

1) Select your data

2) Format > Conditional Formatting > Formula Is

3) Enter the following formula:

=ISNA(A1)

4) Choose your format, in this case "White" for the color of your font

5) Click OK

Change the cell reference in the formula from A1 to the first cell i
the range of data you selected.

Hope this helps!