problem with Cond. Format for a cell containing #N/A

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I used a vlookup function and now some cells return
#N/A. I understand why this is happening but it seems far
to difficult to go into detail about the vlookup funct.
Rather if I could just get conditional formatting to turn
the font within a cell containing #N/A to the color white
then #N/A will not be seen against the white back ground.

Problem: condition formatting does not recognize #N/A as
a value and therefore it will not work.

Is there a remedy.

thank you,
Brian
 
To use conditional formatting, in the first box select FormulaIs; for the formula use
=ISERROR(A1) or =ISNA(A1) and set the font color to white.

But before you go too far with this, be sure your printer handles this correctly, rather than
printing these cells in a black font color.

To modify your formula, you would use something like this:

=IF(ISNA(MATCH(Target,Table,0),"",VLOOKUP(Target,Table,ColNumber,0))
 
This worked as far as making the #N/A disappear, however
it also made the valid returned data within the cells
disappear.
Here is what I used:

=IF(ISNA(MATCH(A8,'Daily Patrol Log'!
$F$22:$L$40,0)),"",VLOOKUP(A8,'Daily Patrol Log'!
$F$22:$L$40,7,0))



-----Original Message-----
To use conditional formatting, in the first box select FormulaIs; for the formula use
=ISERROR(A1) or =ISNA(A1) and set the font color to white.

But before you go too far with this, be sure your printer
Click to expand...
handles this correctly, rather than
printing these cells in a black font color.

To modify your formula, you would use something like this:

=IF(ISNA(MATCH(Target,Table,0),"",VLOOKUP (Target,Table,ColNumber,0))
Click to expand...
 
=IF(ISNA(MATCH(A8,'Daily Patrol Log'!$F$22:$F$40,0)),"",VLOOKUP(A8,'Daily
Patrol Log'!$F$22:$L$40,7,0))

Or in positive mood:

=IF(ISNUMBER(MATCH(A8,'Daily Patrol Log'!$F$22:$F$40,0)),VLOOKUP(A8,'Daily
Patrol Log'!$F$22:$L$40,7,0),"")

If you have the morefunc.xll add-in
(http://longre.free.fr/english/index.html)...

=IF(ISNA(SETV(VLOOKUP(A8,'Daily Patrol Log'!$F$22:$L$40,7,0))),"",GETV())

which is more efficient.

Brian said:
This worked as far as making the #N/A disappear, however
it also made the valid returned data within the cells
disappear.
Here is what I used:

=IF(ISNA(MATCH(A8,'Daily Patrol Log'!
$F$22:$L$40,0)),"",VLOOKUP(A8,'Daily Patrol Log'!
$F$22:$L$40,7,0))



-----Original Message-----
To use conditional formatting, in the first box select FormulaIs; for the formula use
=ISERROR(A1) or =ISNA(A1) and set the font color to white.

But before you go too far with this, be sure your printer
Click to expand...
handles this correctly, rather than
Click to expand...
 
Back
Top