Dynamic Calender

[raccoon588]

I have a calendar on one tab and a list with three columns on another tab. The three columns are NAME, DATE OFF, and APPROVED. If the APPROVED column has a yes in it i would like to have the name placed into the calendar bellow the corresponding date. sometimes there may be more then one name assigned to a date.

any ideas on how to go about this?

 

[Perpa]

raccoon588,
You might give this calendar a try...I have used it successfully for several different applications.
Download the macro enabled file from this link:
https://app.box.com/s/b5u2l6zwmphmj6peeuuog7rzhzz7p63b
Save this file with your filename.


Beginning in row 6 (Header Row) of the calendar sheet (Sheet1) thereof,
- Copy your DATE OFF column to column J of the calendar sheet.
- Copy your NAME column to column K of the calendar sheet.
- Copy your APPROVED column to column L of the calendar sheet.


Save your file as macro enabled, .xlsm file extension.


Press Alt+F11 and locate the macro named 'MyHolidays' in Module6.
Change the line of code that reads:

   If Cells(4, "J") = myMonth Then

To this:

   If Cells(4, "J") = myMonth And Cells(rw1, "L") = "Yes" Then

Close the Visual Basic Editor and Save the file.


Manually change the year to the year you want, 2018? You will have to do this each year.
Make sure macros have been enabled, you may have to do this each time you open the file depending
on the version of Excel you are running.


Now change the month by selecting it from the drop down in cell J4.
The dates with 'Yes' in column L should be highlighted and shown in the respective dates of this calendar as shown below.
Perpa


Sheet1

B

C

D

E

F

G

H

I

J

K

L

M

Select Month from Drop Down

JULY

in this box

DATE OFF

NAME

APPROVED

George

Yes

Tex

Sam

Yes

Mike

Yes

Tim

Tom

<tbody>
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: left"]2018[/TD]

[TD="align: left"]JULY[/TD]
[TD="align: left"]<=======[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]SUNDAY[/TD]
[TD="align: center"]MONDAY[/TD]
[TD="align: center"]TUESDAY[/TD]
[TD="align: center"]WEDNESDAY[/TD]
[TD="align: center"]THURSDAY[/TD]
[TD="align: center"]FRIDAY[/TD]
[TD="align: center"]SATURDAY[/TD]

[TD="align: left"]7/3/2018[/TD]

[TD="align: center"]8[/TD]
[TD="align: left"]1[/TD]
[TD="align: left"]2[/TD]
[TD="align: left"]3[/TD]
[TD="align: left"]4[/TD]
[TD="align: left"]5[/TD]
[TD="align: left"]6[/TD]
[TD="align: left"]7[/TD]

[TD="align: left"]7/4/2018[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]George[/TD]

[TD="align: left"]7/10/2018[/TD]

[TD="align: center"]10[/TD]

[TD="align: left"]7/20/2018[/TD]

[TD="align: center"]11[/TD]
[TD="align: left"]8[/TD]
[TD="align: left"]9[/TD]
[TD="align: left"]10[/TD]
[TD="align: left"]11[/TD]
[TD="align: left"]12[/TD]
[TD="align: left"]13[/TD]
[TD="align: left"]14[/TD]

[TD="align: left"]7/30/2018[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]Sam[/TD]

[TD="align: left"]7/30/2018[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]
[TD="align: left"]15[/TD]
[TD="align: left"]16[/TD]
[TD="align: left"]17[/TD]
[TD="align: left"]18[/TD]
[TD="align: left"]19[/TD]
[TD="align: left"]20[/TD]
[TD="align: left"]21[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]Mike[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]
[TD="align: left"]22[/TD]
[TD="align: left"]23[/TD]
[TD="align: left"]24[/TD]
[TD="align: left"]25[/TD]
[TD="align: left"]26[/TD]
[TD="align: left"]27[/TD]
[TD="align: left"]28[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]
[TD="align: left"]29[/TD]
[TD="align: left"]30[/TD]
[TD="align: left"]31[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]23[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Perpa]

raccoon588,
The image below is a better representation of the finished look. Don't know why Excel Jeanie distorts everything like that.
Perpa