Conditional Formatting Question

[mrexcelrc]

Hi,
I created this calendar below using formulas. I also have a table of all holidays in another tab. Using "Use a formula to determine which cells to format" under Conditional Formatting, how would use it? For example, if I use formula functions vlookup and isnumber to lookup September 5 (cell B3) in the Table of Holidays and see if the value on column 3 (72) is a number which in this case is true, then format B3 by filling it with color yellow. Please help.





A B C D E F G
SEPTEMBER
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30

Table of Holidays
9/5/2011 Mon 72 Labor Day

 

[cummingsea]

Hi,
I created this calendar below using formulas. I also have a table of all holidays in another tab. Using "Use a formula to determine which cells to format" under Conditional Formatting, how would use it? For example, if I use formula functions vlookup and isnumber to lookup September 5 (cell B3) in the Table of Holidays and see if the value on column 3 (72) is a number which in this case is true, then format B3 by filling it with color yellow. Please help.




A B C D E F G
SEPTEMBER
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30

Table of Holidays
9/5/2011 Mon 72 Labor Day

Go here: http://office.microsoft.com/en-us/t...x=2&qu=Eugene Cummings&av=all#ai:TC030002398|
you might like this calendar with holidays.

 

[mrexcelrc]

1. The result is FALSE if I put this formula.
=ISNUMBER(INDEX(NY_holidays,MATCH(DATEVALUE($B$54&" "&B55&", 2011"),dates,0)))

2. The result is #VALUE if put formula.
=INDEX(NY_holidays,MATCH(DATEVALUE($B$54&" "&C56&", 2011"),dates,0))

3. The result is #VALUE if put formula.
=MATCH(DATEVALUE($B$54&" "&C56&", 2011"),dates,0)

4. The result is #VALUE if put formula.
=DATEVALUE($B$54&" "&C56&", 2011")

IS IT BECAUSE THE FORMULA IN CELL C56 AND THE OTHERS IS?:
=IF(AND(YEAR(SepSun2+8)=$P$36,MONTH(SepSun2+8)=9),SepSun2+8, "")

 

[mrexcelrc]

But if I put the below formula, it returns 72.

=IF(ISNUMBER(VLOOKUP(C56,BB:BC,2,FALSE))=TRUE,VLOOKUP(C56,BB:BC,2,FALSE),"")

 

[mrexcelrc]

But if I put the below formula, it returns 72.

=IF(ISNUMBER(VLOOKUP(C56,BB:BC,2,FALSE))=TRUE,VLOOKUP(C56,BB:BC,2,FALSE),"")


THE VALUE IS "&C55&" IF i SELECT CELL C56.
Confirm for me that when you select cell C56 (value of 5) that it has this exact CF formula so I know we're on the same page.
Formula is =ISNUMBER(INDEX(NY_holidays,MATCH(DATEVALUE($B$54&" "&C56&", 2011"),dates,0)))

 

[mrexcelrc]

I think I know what the problem is. In Cell 56 that contains the formula below, if I just type 5, the conditional formatting formula works. What should I do?




=IF(AND(YEAR(SepSun2+8)=$P$36,MONTH(SepSun2+8)=9),SepSun2+8, "")