Calendar and database

b_book1.gif (162 bytes)Guides - MS-Excel97
ssxl97_0.gif (913 bytes) Model
Demonstrates: Formatting, Formulae, VBasic
l_stone.gif (2795 bytes)

xl97cal.gif (18153 bytes)

Creating the calendar.

  • Worksheet tab
    • Double click and name "Calendar"
  • [A1]
    • Enter first day of month (eg. 1/1/98).
    • Format|Cell|Number
      • Custom:Category[mmmm yyyy]
      • Font: 18 point.
  • [A1:G1]
    • Select and centre across columns (xl97_bc.gif (872 bytes))
  • Row 2 & 3
    • Enter Days of week (Row 2) and
    • Sunday.. Saturday (Row 3)
  • [A4]
    • Enter the formula
      =IF(WEEKDAY($A$1)>A2,"",$A$1+A2-WEEKDAY($A$1))
    • Set format to "Custom" d
    • Copy to B4:G4
  • [A6]
    • Enter formula
      =G4+1
  • [B6]
    • Enter formula
      =A6+1
    • Copy to C6:G6
  • [A6:G6]
    • Select and copy A8:G8 and A10:G10
  • [A1:G10]
    • Since the last day may occur in either row 12 or 14 a formula based range is required.
    • Select A1:G10 and choose Insert|Name|Define
      • Names Workbook box enterNames
      • Refers to box enter formula
        =Calendar!$A$1+DAY(DATE(YEAR(Calendar!$A$1),MONTH(Calendar!$A$1)+1,1)-1)
  • Row 12 and 14
    • Select (Use <Ctrl> Key.)
    • Format days to "Custom" d
  • [A12]
    • Enter formula
      =IF(A10+7>=LastDay,"",A10+7)
    • Copy formula to B12:G12
  • [A14]
    • Enter formula
      =IF(A10+14>=LastDay,"",A12+7)
    • Copy formula to B14:G14
  • Format Calendar Headings
    • Turn off grid lines
    • Paint borders
    • Set column widths (=14)
  • Row 5
    • Format Cells ;Vertical Alignment = top and Wrap text
    • Format|Row|Height = 35
  • Row 4 & 5
    • Highlight (complete row)
    • Use format painter to format rows 6-7, 8-9, 10-11, 12-13 and 14-15.
  • Save worksheet

Change the date in A1 for a new calendar.

b_ref.gif (1385 bytes)Ref: Scoville, R. (1997, Jul)

xl97cal1.gif (9469 bytes)

Displaying data in the calendar

  • Select data cells [A5:G5] and [A7:G7]  to [A15:G15] (using <Ctrl> key).
  • Choose Insert|Name|Define and enter CalendarDays
  • On another sheet create a database as shown (Left).
    • Name the sheet "ListLookup"
  • Create the macro
    • Select Tools| Macro| Visual Basic Editor
      Select Insert Module and enter the "Fill days module" following.
  Sub FillDays()
  Dim PeopleDates, CalendarDays As Range
  'Identify the calendar cells to fill, and the database
  Set CalendarDays = Worksheets("Calendar").Range("CalendarDays")
  Set PeopleData = Worksheets("ListLookup").Range("A2:b25")
  'Do the following for each cell in the calendar
  For Each c1 In CalendarDays.Cells
    'Do the following for each date in the list
    For Each c2 In PeopleData.Resize(PeopleData.Rows.Count, 1)
      'If the current date in teh list matches the current calendar day
      If c1.Offset(-1, 0).Value = c2.Value Then
        '.. enter the corresponding list item into the calendar
        c1.Formula = c2.Offset(0, 1).Formula
      End If
    Next c2
  Next c1
End Sub
 
  • Close the VB Editor and Return to the calendar worksheet
  • Run the FillDays Macro.

Note: the worksheet has been slightly modified from original.

b_ref.gif (1385 bytes)Ref: Scoville, R. (1997, Aug)

[Rev 8/2/98] 8/2/98 © 1998 V/2-Com (Verhaart), P O Box 8415, Havelock North, New Zealand.