Seeking a 'special' calendar template

[Lobster]

I'm looking for a specific calendar template for use with Excel 2003.

It's for use as a meeting planner, and what I want ideally is to be able
to have one worksheet displaying a standard calendar view something like
this one: http://tinyurl.com/bmnetp (OR
http://office.microsoft.com/en-us/templates/TC300041371033.aspx?CategoryID=CT102774121033&av=ZXL000
)
....where I can add an entry for a meeting into one date within a
displayed month, and that links to a row in a second worksheet within
the same workbook which contains details of all the meetings. That
worksheet would have columns for categories like "Date" "Time" "Title",
"Speaker", "Speaker invited Y/N?", "Speaker fee" etc etc

The cell in the 'calendar' worksheet would contain just "Speaker" and
"Title", say.

It would be easy to set up these as two entirely separate worksheets -
ie a 'calendar' one and a 'meeting details' one; and I could achieve
what I want by inserting manual links between the two, for every single
meeting, but it would be prohibitively fiddly and time-consuming: is
there an off-the-shelf solution?

Thanks
David

 

[Shane Devenshire]

Hi,

You could write a macro to transfer the data. You would need a consistant
entry style, for example Speaker - Title. Suppose you entered Titles in A1
and B1 of sheet2. Then something like this:

Here is some sample code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("Input"))
If Not isect Is Nothing Then
With Sheets("Sheet2")
If .Range("A2") = "" Then
.Range("A2") = Left(Target, InStr(1, Target, "-") - 1)
.Range("B2") = Mid(Target, InStr(1, Target, "-") + 1)
Else
.Range("A1").End(xlDown).Offset(1, 0) = Left(Target,
InStr(1, Target, "-") - 1)
.Range("B1").End(xlDown).Offset(1, 0) = Mid(Target, InStr(1,
Target, "-") + 1)
End If
End With
End If
End Sub


1. To add this code to your file, press Alt+F11,
2. In the VBAProject window, top left side, find your sheet name under your
file name and double click it.
3. Paste in or type the code above.

For this example you will need to select all the dates on all the months
(using the Ctrl key) and then name that selection Input by clicking in the
Name Box, left side of the Formula Bar, typing in the name and pressing Enter.

Modify as needed.