Excel '97: VLOOKUP errors and Conditional Formatting

[emboyd]

Hi Guys and Gals,

I'm creating a summary worksheet which references a rollup worksheet by date with VLOOKUPS. My formulas are setup to get a rolling 14 days, but the source data only has weekdays. This is fine, but I would like to use a Conditional Format to "blackout" the cells on the summary page where the return value is "#N/A", which is caused because the referenced date is a Saturday or Sunday. So far, I've been unsuccessful with making this work.

Any ideas are appreciated.


Eric M. Boyd
Business Systems Analyst
Washington Mutual Bank

 

[PeterMoran]

Hi Eric,

A slight alternative to what you are suggesting is to include the LOOKUP in an IF statement along the following lines:

IF(ISERROR(VLOOKUP(.......)),"",VLOOKUP(...same details as prev))

This may be a little longwinded but it leaves the result as blank (or whatever text you want) when there is any error in the lookup, otherwise you get the result you want.

Also if you just want to trap the N/A result you can change the ISERROR in the statement to ISNA.

Its all in the formula and you don't have any Conditional Formatting to be worried about.

Good Luck!

Peter Moran

 

[MisterC]

Just set your conditional format to change the font color and background (fill) color of the cell to black, or white, or whatever color... if the value =NA()

 

[MisterC]

OOPS, nope that won't work. sorry...

 

[MisterC]

Here you go:
Change the Conditional Format to "Formula Is" and set it =ISNA(x) Where x is the cell address.