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 lookin
up in returns a "#N/A" cell value. No problem there. However, I'd lik
to hide these values, and just keep the returned VLOOKUP values.

I thought this would be done easiest using conditional formatting o
the cells, and setting the font colour to white if the cell value wa
"#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 i
inverted commas, and also typing #N/A in a cell (let's say A50) an
doing:

Cell Value / equal to / $A$50

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

TIA,

Samuel

 

[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]

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]

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