Shade Cells and populate list in Excel calendar

**staysecure** · 01-04-2013, 12:54 PM

Thanks in advance for any assistance. Attached is a calendar that I am trying to do two things after selecting a country name from the drop down to populate office holiday information:

1. Shade the appropriate date in the calendar to indicate a holiday (maybe shade orange)
2. Populate the list on the side with all of the dates and holiday names listed for that country

I tried doing some vlookups but couldn't get it to work and I couldn't nail down a formula for conditional formatting.

Thanks again in advance for any assistance.

**Pete_UK** · 01-04-2013, 01:42 PM

You will need to change your dates in column B of Sheet1 into proper Excel dates rather than text values.

Pete

**JBeaucaire** · 01-04-2013, 01:58 PM

1) put this formula in Z5 and copy down and across to column AA as well
=IF(ROW(A1)>COUNTIF(Sheet1!A:A, $F$1), "", INDEX(Sheet1!B:B, MATCH($F$1, Sheet1!A:A, 0) + ROW(A1)-1))

2) color column Z WHITE to hide the dates that appear leaving only the column Z text visible.

3) Highlight B7:H12 (the dates in January)

4) Apply this conditional formatting rule (1 = January):

=AND(ISNUMBER(B7), ISNUMBER(MATCH(TEXT(DATE($B$3, 1, B7), "DDDD, MMMM DD, YYYY"),$Z:$Z, 0)))

5) repeat the process with J7:P12 but change the formula (2 = February):

=AND(ISNUMBER(J7), ISNUMBER(MATCH(TEXT(DATE($B$3, 2, J7), "DDDD, MMMM DD, YYYY"),$Z:$Z, 0)))

6) Etc... (you'll need to continue this in the sample sheet below)

7) I moved the country list in column D up to D1 and added a dynamic named formula COUNTRIES which was then applied to to cell F1 for a "drop down list". As you add more countries in column D of Sheet1 the drop down will expand itself.

8) The formulas for the dates in Z:AA only work because your sheet1 A:C is sorted properly. Make sure you keep that data sorted like that as you expand it.

**staysecure** · 01-04-2013, 02:54 PM

@JBeaucaire, that is brilliant. Thank you for the assistance.

**staysecure** · 01-04-2013, 04:38 PM

@JBeaucaire, in fitting out the rest of the sheet, the colored cell dates were off from the list. I figured out that in addition to changing the month reference, that I needed to change the calendar cell reference as well. For instance For February I need to use J7 instead of B7. Can you tell me why this would be? Eager to learn. Thanks.

**Pete_UK** · 01-04-2013, 09:15 PM

My approach is a bit different than Jerry's. I've put proper dates in column B of Sheet1 and formatted the cells to appear like you had them (so, if you add any more data ensure that you just put a date in, i.e. 1/1/2013). Note that you have some duplicates in here - check out Ireland and Russia in particular.

I've used a helper column in Sheet1 (D) with this formula in D2:

Formula:

Please Login or Register  to view this content.

and this is copied down beyond the bottom of your data, with the hyphens showing how far it has been copied - copy further if you add more data.

On the calendar sheet I've given you a drop-down on row 1 (yellow cells) so that you can choose your country from the list on Sheet1. I've used this formula in AA5:

Formula:

Please Login or Register  to view this content.

to give you the (abbreviated) date and details, and I've applied conditional formatting to highlight each holiday on the calendar, using this type of formula for Jan 1st:

Formula:

Please Login or Register  to view this content.

The Format Painter can be used to apply this to other cells. For other months the part in red needs to change to J and R, and to 14, 23, and 32, as appropriate.

Just use the drop-down to select your country, and see the details and colours change.

Hope this helps.

Pete

**JBeaucaire** · 01-04-2013, 10:54 PM

Yes, I have corrected the step #5 in my post above. Good catch.

As you do each new section you will set the B7, J7 etc to the address of the first cell in the highlighted range. This is a relative reference and will adjust itself in all the formulas applied to the highlighted section. In cell B7 it will say B7, but if you click on C7 and check it, it will say C7 in that cell, etc. That's how relative references work and that's how the formula is able to construct a date reference using the number value in each individual cell.

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

**staysecure** · 01-04-2013, 11:07 PM

@JBeaucaire and @Pete_UK, thanks both for the help. I'm going to play around with each of the solutions to see which one I can break quicker and then try and fix myself. Thanks for growing my knowledge. Regards.