Conditional Formatting and Errors

**Jonathan78** · 05-29-2010, 03:58 AM

Hello all,

I made a calendar that collects data from another tabsheet by looking at the month and daynumber. When there is no occurrence it displays a #N/A.

I thought about using =IF(ISERROR(OriginalFormula),"",OriginalFormula) to hide the errors but the formula is too cumbersome for that.

Another possibility would be using conditional formatting =ISNA(A1), but, not all cells have the same color, which makes it quite difficult to apply.

Another conditional formatting is in use for displaying weekends by looking at the year.

The attachment will show what I mean

Any ideas?

**contaminated** · 05-29-2010, 04:28 AM

Try this formula in C5
=LOOKUP(REPT("z",255),CHOOSE({1,2},"",RIGHT(INDEX(SHEET1!$K$3:$IV$253,MATCH($B$2,SHEET1!$B$3:$B$253,0),MATCH(DATEVALUE(C$4&"/"&$B5&"/"&$B$4),SHEET1!$K$1:$IV$1,0)),IF(LEN(INDEX(SHEET1!$K$3:$IV$253,MATCH($B$2,SHEET1!$B$3:$B$253,0),MATCH(DATEVALUE(C$4&"/"&$B5&"/"&$B$4),SHEET1!$K$1:$IV$1,0)))=3,2,1))))

copied down then accross

**DonkeyOte** · 05-29-2010, 04:31 AM

FWIW it seems there's an issue with how you intend to handle the numerics eg 24th May.

Using your present set-up (which ignores numerics) you could shorten to:

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",REPLACE(INDEX(SHEET1!$K$3:$IV$253,MATCH($B$2,SHEET1!$B$3:$B$253,0),MATCH(DATE($B$4,MONTH(1&$B5),C$4),SHEET1!$K$1:$IV$1,0)),1,1,"")))

**Jonathan78** · 05-29-2010, 05:05 AM

Originally Posted by DonkeyOte

FWIW it seems there's an issue with how you intend to handle the numerics eg 24th May.

Using your present set-up (which ignores numerics) you could shorten to:

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",REPLACE(INDEX(SHEET1!$K$3:$IV$253,MATCH($B$2,SHEET1!$B$3:$B$253,0),MATCH(DATE($B$4,MONTH(1&$B5),C$4),SHEET1!$K$1:$IV$1,0)),1,1,"")))

Hello DonkeyOte,

This works almost... It does not show the "Z", "C" or the "V" however
Too be honest, I find your formula quit difficult too solve this myself.

BTW, If this shortens the formula I rather use the numbers in row A for the months iso collumn B

**DonkeyOte** · 05-29-2010, 05:08 AM

Did you try Contaminated's - I think that will work for you, no ?
(you might need to change the date separator pending locale settings... also note the superfluous spaces in the formula - inserted by the board software unfortunately)

edit: if you use the month numbers you can use DATE rather than DATEVALUE

**Jonathan78** · 05-29-2010, 01:40 PM

Contaminated, your formula works fine indeed.
Something must have gone wrong the first time I looked at it.
Thanks again

One question though, what does the LOOKUP(REPT("z";255 do?