Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2013
    Posts
    1
    Thanks
    1
    Thanked 1 Time in 1 Post

    list and calendar on excel

    hi... i need help with excel..

    i made list with names and expiry dates and download a calendar from some website. what i wanna do is that names should be placed on the calendar and would correspond with the expiry date.
    Attached Files Attached Files

  2. The Following User Says Thank You to shieyla For This Useful Post:

    Maudibe (2013-11-01)

  3. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    In G5 of November, I wrote: =IFERROR(INDEX(Sheet1!$B$1:$B$105,MATCH(DATE(calen darYear,11,DAY(F4)),Sheet1!$G$1:$G$105,0),1),"")
    Then, copied and pasted that in the other date blocks for November.

    But, this only puts the first one of possibly more than 1 in the cell.

    It seems laborious to have to do this for all 12 months, so, maybe there's a VBA solution that someone will come up with.
    Last edited by kweaver; 2013-10-31 at 14:33.

  4. The Following 2 Users Say Thank You to kweaver For This Useful Post:

    Maudibe (2013-11-01),shieyla (2013-10-31)

  5. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    KW,
    Well done with the formula! You are right, VBA is the way to go here.

    Shieyla,

    You have done a marvelous job with this spreadsheet...very clean. I will keep a copy of this tucked away as I see many applications for its use. What a pleasure working on it!

    The code is activated by changing the year in cell k5 with the spinner button. It will search the list of names with a signoff date in that year and place them on the appropriate day of the appropriate calendar month. Simply changing the year updates the workbook. There is a "Clear Calendar" button if you wish to have a cleared workbook, however, there is no need to clear it before changing the year. The code will do that automatically.

    HTH,
    Maud

    CrewChanges1.png CrewChanges2.png

    Code:
    Public Sub ListPerson()
    Application.ScreenUpdating = False
    With Worksheets("Sheet1")
    '-------------------------------------------------
    'DECLARE AND SET VARIABLES
    Dim rng As Range
    Dim cell As Range
    'Dim Tdate As Date
    Clearsheets
    '-------------------------------------------------
    'GET DATE AND BREAK INTO COMPONENTS
    LastRow = .Cells(Rows.Count, 7).End(xlUp).Row
    For I = 2 To LastRow
        Tdate = .Cells(I, 7).Value
        Tmonth = Month(Tdate)
        Tday = Trim(Str(Day(Tdate)))
        Tyear = Year(Tdate)
    '-------------------------------------------------
    'FILTER BY YEAR THEN FIND CORRECT MONTHLY CALENDAR
        If Tyear = Worksheets("Jan").[k5].Value Then
            Select Case Tmonth
            Case 1
                Worksheets("Jan").Activate
            Case 2
                Worksheets("Feb").Activate
            Case 3
                Worksheets("Mar").Activate
            Case 4
                Worksheets("Apr").Activate
            Case 5
                Worksheets("May").Activate
            Case 6
                Worksheets("Jun").Activate
            Case 7
                Worksheets("Jul").Activate
            Case 8
                Worksheets("Aug").Activate
            Case 9
                Worksheets("Sep").Activate
            Case 10
                Worksheets("Oct").Activate
            Case 11
                Worksheets("Nov").Activate
            Case 12
                Worksheets("Dec").Activate
            End Select
    '-------------------------------------------------
    'SEARCH FOR MATCH WITH THE DAY AND PLACE NAME
            Set rng = ActiveSheet.Range("B4:H14")
            For Each cell In rng
                Debug.Print cell.Address
                If cell.Value = Tdate Then
                    Debug.Print Tday & " " & cell.Address
                    cell.Offset(1, 0).Value = .Cells(I, 2).Value
                End If
            Next cell
            Worksheets("Jan").Activate
    '-------------------------------------------------
        End If
    Next I
    End With
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •