Hiding #N/A With Conditional Formatting

[SamuelT]

Hi all,

I've got a VLOOKUP list that, if nothing is in the a cell it is looking up in returns a "#N/A" cell value. No problem there. However, I'd like to hide these values, and just keep the returned VLOOKUP values.

I thought this would be done easiest using conditional formatting on the cells, and setting the font colour to white if the cell value was "#N/A". I tried the following condition:

Cell Value / equal to / #N/A

But Excel doesn't seem to like this. I've also tried the #N/A in inverted commas, and also typing #N/A in a cell (let's say A50) and doing:

Cell Value / equal to / $A$50

Still no joy. Can anyone suggest a means of hiding my #N/As?

TIA,

SamuelT

[Pete]

One way is to change your lookup formula to detect if you get errors,
and to return "" if this happens, i.e.

=IF(ISNA(lookup_formula),"",lookup_formula)

This will check for any #N/A and substitute a blank instead. You could
use ISERROR instead of ISNA.

Pete

[Stephen]

"SamuelT" <[email protected]> wrote in
message news:[email protected]...
>
> Hi all,
>
> I've got a VLOOKUP list that, if nothing is in the a cell it is looking
> up in returns a "#N/A" cell value. No problem there. However, I'd like
> to hide these values, and just keep the returned VLOOKUP values.
>
> I thought this would be done easiest using conditional formatting on
> the cells, and setting the font colour to white if the cell value was
> "#N/A". I tried the following condition:
>
> Cell Value / equal to / #N/A
>
> But Excel doesn't seem to like this. I've also tried the #N/A in
> inverted commas, and also typing #N/A in a cell (let's say A50) and
> doing:
>
> Cell Value / equal to / $A$50
>
> Still no joy. Can anyone suggest a means of hiding my #N/As?
>
> TIA,
>
> SamuelT

You can do this with conditional formatting, using the ISNA() function, but
it's better to adapt the formula itself, like this:
=IF(ISNA(your_formula),"",your_formula)

For example:
=IF(ISNA(VLOOKUP(D1,A1:B3,2,FALSE)),"",VLOOKUP(D1,A1:B3,2,FALSE))

Stephen

[Danny@Kendal]

"SamuelT" <[email protected]> wrote in
message news:[email protected]...
>
> Hi all,
>
> I've got a VLOOKUP list that, if nothing is in the a cell it is looking
> up in returns a "#N/A" cell value. No problem there. However, I'd like
> to hide these values, and just keep the returned VLOOKUP values.
>
> I thought this would be done easiest using conditional formatting on
> the cells, and setting the font colour to white if the cell value was
> "#N/A". I tried the following condition:
>
> Cell Value / equal to / #N/A

If you compare one value to an error you get an error rather than
true/false.

Select the range of cells to which you want to apply the conditional
formatting then use the following:
Formula Is =ISERROR(A1)

Replace A1 with the top-left cell in your selected range.

[SamuelT]

Thanks guys.

Went with the last one. Works a treat!

SamuelT