Anniversaries in an excel generated Calendar

[DavidObeid]

I have a spreadsheet that will generate a calendar for any given year.

If a user has a list of anniversaries that he wants placed in th
calendar (eg: Dad's B'day, Wedding Anniversary etc) automatically, ho
exactly do I set that up?

The anniversaries will go in columns D, H, L, etc corresponding to Jan
Feb, Mar, etc

With my sincere promise that I will buy a VBA book before the week i
out, could I ask if this can be done without VBA for the time being?

Here is what the first few cells of the calendar look like:

A B C

1 January 2004
2 1 1/Jan/2004 Thursday
3 2 2/Jan/2004 Friday
4 3

Regards,

Dav

 

[Eric]

David,

One way would be to create a separate table of "key dates"
on a separate tab. Something like:

A B
1 7/4/2003 Independence Day
2 7/15/2003 Dad's birthday
3 10/31/2003 Halloween

Using the model you listed, create a VLOOKUP function in
column D. Something like (all in one cell):

=IF(ISERROR(VLOOKUP($B2,KEYDAYS!
$A$6:$B$8,2,FALSE)),"",VLOOKUP($B2,KEYDAYS!
$A$6:$B$8,2,FALSE))

You have to include the ISERROR command to avoid getting
the #N/A value for dates that don't match your Key Dates
tab.

Hope that helps!

Eric

 

[Jim]

I would use data validation.
Create a list of the dates of your holidays/observances in a range of cells
and name it.
Select the cells that occupy the calendar, assuming you are using a calendar
created in the worksheet cells. Let's say the calendar begins on the top
left with A2. With the cells selected, Format>Conditional Formatting>Formula
Is>
=COUNTIF(MyHolidays,A2)>0
and format for the change if the date is within the calendar.

If you can't get it to work, I can send you a calendar that uses this
method.

 

[DavidObeid]

Dear Eric,

I am still having problems.

Would it be ok if I sent you the file I'm working on?

Dav