Conditional Formatting Question (Hiding "#N/A" err

[Excel Bunny]

Hello All,

This board has been so helpful in the past so I thought I would try my luck once again.

I am working on a table that drags data from other worksheets using VLOOKUP so sometimes fields in this table show up "#N/A" errors (these are instances when the lookup value cannot be found in the source file).

What I want to do is set Conditional Formatting to display all "#N/A" errors as white text, hence rendering them invisible. I assume a formula will be required but I'm not sure which one. Any ideas on this matter would be much appreciated.

Thank you to the regular posters for providing solutions to a variety of Excel dilemmas - I have learnt much just by scanning through past entries. Cheers.

 

[gra_ob]

Hi Excel Bunny,

You could do it the other way round and just format the whole column as white font. Then set conditional formatting for the whole column where cells are greater than or equal to 0 appear in black font.

 

[barry houdini]

using conditional formatting you can simply use

formula is

=ISNA(A1)

and set format to white font

 

[Excel Bunny]

Hi Gra,

Thanks for the prompt reply. My only concern is that this table deals predominantly with text values, not numbers. Is there something I can do with "ISNA" in an IF function?

Cheers

 

[Excel Bunny]

Thanks for the reply Barry

I tried your suggestion, replacing A1 with the range I want to apply the conditional formatting to, but it doesn't seem to have worked:

This is what I tried in the Conditional formatting formula section:

=ISNA($D16:$E27)

Any suggestions as to what this formula is missing? Many thanks

 

[gra_ob]

The conditional formatting should still recognise text.

But if you'd prefer to insert another column then you could do something like this:

=IF(ISNA(A1),"",A1)

i.e. that will look up the value in A1 - if it is #N/A then it will leave the cell blank, otherwise it will bring back the value in the cell.

 

[barry houdini]

You need to apply the conditional formatting to one cell only...like this

If you select your whole range D16:E27 with D16 active cell then set conditional formatting to

formula is

=ISNA(D16)

 

[Ste_Moore01]

Alternatively you can replace the A1 with your Vlookup formula so you don't have to have another cell with a formula in.

 

[Peter_SSs]

Excel Bunny

If you are trying to make the #N/A results invisible, what about eliminating them altogether rather than making them white? To do this, adjust your existing VLOOKUP formula as follows:
=IF(ISNA(Your vlookup formula),"",Your vlookup formula)

 

[Excel Bunny]

Peter, Ste_Moore and Barry,

Thanks for taking the time to help me out. I've got it working the way I want to now.

Peter, your solution to get rid of N/A errors altogether is absolute gold and will come in very handy from now on.

<End of Question>