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

 

[AlphaFrog]

Conditional formatting doesn't accept direct references to cells on other worksheets. A simple way around this is to Name the Ranges you want to reference on the other sheets, and then use the Names within the Conditional formatting formulas.

Named Ranges

In this example, Sheet 2 columns A to D has your holiday list.
Sheet2 column A (dates) is a named range called Sheet2AA
Sheet2 column C (numbers e.g. 72) is named Sheet2CC

Select the calendar cells A2:G6 on your calandar sheet
This is the conditional formatting formula.
Formula is =ISNUMBER(INDEX(Sheet2CC,MATCH(DATEVALUE($A$1&" "&A2&", 2011"),Sheet2AA,0)))
Select a yellow background fill pattern

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">SEPTEMBER</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;background-color: #FFFF99;;">5</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;">8</td><td style="text-align: center;;">9</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">11</td><td style="text-align: center;;">12</td><td style="text-align: center;;">13</td><td style="text-align: center;;">14</td><td style="text-align: center;;">15</td><td style="text-align: center;;">16</td><td style="text-align: center;;">17</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">18</td><td style="text-align: center;;">19</td><td style="text-align: center;;">20</td><td style="text-align: center;;">21</td><td style="text-align: center;;">22</td><td style="text-align: center;;">23</td><td style="text-align: center;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">25</td><td style="text-align: center;;">26</td><td style="text-align: center;;">27</td><td style="text-align: center;;">28</td><td style="text-align: center;;">29</td><td style="text-align: center;;">30</td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

 

[mrexcelrc]

Hello,
This is not working possibly due to error in this formula. Shouldn't there be a " in front of $A$1&" and then a comma. Also I think there should be a " in front of 2011". In my example cell A1 has a text value "SEPTEMBER". Would that work?



Formula is =ISNUMBER(INDEX(Sheet2CC,MATCH(DATEVALUE($A$1&" "&A2&", 2011"),Sheet2AA,0)))

 

[AlphaFrog]

Shouldn't there be a " in front of $A$1&" and then a comma.

No

Also I think there should be a " in front of 2011"

There is a quote in front.
", 2011"

In my example cell A1 has a text value "SEPTEMBER". Would that work?

Yes it should work if no spelling errors.


This ($A$1&" "&A2&", 2011") creates a date as text string something like September 5, 2011 and the DATEVALUE function then converts it to a serial date. That serial date is then matched in the named range Sheet2AA. If a match is found, then the value from the named range Sheet2CC is returned (same row as the matched date) and tested if it is a Number.

My guess is the dates in Sheet2AA are not serial dates (they are just text) and then wouldn't return a Match. Or the named ranges are not set.

 

[mrexcelrc]

Yes, they are text. Below is an example. So do I need to format the dates as serial dates. What should I do?




9/5/2011

 

[AlphaFrog]

Is this what you see in the formula bar when you select the cell with this date?
9/5/2011
If yes, then they are serial dates and not text.

On the calendar sheet. if you select cell B3 (it has a value of 5) and look its' conditional formatting formula, is it exacltly this?
=ISNUMBER(INDEX(Sheet2CC,MATCH(DATEVALUE($A$1&" "&B3&", 2011"),Sheet2AA,0)))

 

[mrexcelrc]

OK, I'm really sorry I'm having a hard time here. Below is my actual data. "SEPTEMBER" is on cell B54. For cells B54:H60 the following is my conditional formatting formula.

=ISNUMBER(INDEX(NY_holidays,MATCH(DATEVALUE($B$54&" "&B55&", 2011"),dates,0)))

If I select cell C56 (value of 5) the conditional formatting formula is the following:
ISNUMBER(INDEX(NY_holidays,MATCH(DATEVALUE($B$54&" "&B55&", 2011"),dates,0)))

It doesnt work with this formula. Can you tell what's wrong with this? Thanks.


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

 

[AlphaFrog]

When you selected the cells (B54:H60) to apply the CF formula , the cell reference in Red
=ISNUMBER(INDEX(NY_holidays,MATCH(DATEVALUE($B$54&" "&B55&", 2011"),dates,0)))
...has to be the upper left cell in your selection. You selected B54 but used B55 in the formula(or something like that).

So select cells B55:H60 and apply the CF formula
=ISNUMBER(INDEX(NY_holidays,MATCH(DATEVALUE($B$54&" "&B55&", 2011"),dates,0)))

If you've applied the CF formula correctly, the red cell reference will be the same as the cell that the CF formula is in for each cell in the calendar.

 

[mrexcelrc]

Still not working. I think it's the way I defined the named ranges. Column BB is "dates" and column BC is NY_holidays. Using Index Match function I have to name columns BB and BC as one named range, correct?




BB BC
8/30/2011 g
8/31/2011 g
9/1/2011 g
9/2/2011 g
9/3/2011 w
9/4/2011 w
9/5/2011 72
9/6/2011 g
9/7/2011 g
9/8/2011 g
9/9/2011 g
9/10/2011 w
9/11/2011 w
9/12/2011 g
9/13/2011 g

 

[AlphaFrog]

Using Index Match function I have to name columns BB and BC as one named range, correct?

Incorrect. I think you've done it correctly as two different named ranges.

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)))

Then tell me what the Refers to: formula is for the two named ranges. Ctrl+F3 brings up the Define Name dialog where you can see the Refers to: formulas.

As a diagnostic, if you put this formula in any cell
=ISNUMBER(INDEX(NY_holidays,MATCH(DATEVALUE($B$54&" "&C56&", 2011"),dates,0)))
It should return TRUE as a result.

You could then breakdown the formula to see where the problem is e.g. this should return the value 72
=INDEX(NY_holidays,MATCH(DATEVALUE($B$54&" "&C56&", 2011"),dates,0))

This should return the row number of the matched date in the named range date
=MATCH(DATEVALUE($B$54&" "&C56&", 2011"),dates,0)

This should return the serial date 9/5/2011
=DATEVALUE($B$54&" "&C56&", 2011")