Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Need formula, please ? (excel 2003; Win XP)

    I am trying to set up some Data Validation; I have a column of dates and for each day, there is a row (on the same sheet, but 75 or 80 columns away) that lists employees who are absent on that particular day.

    I could do the usual Data Validation using a drop-down list that 'reads' the names in the row of Absent employees, but I was looking for some way to set it up so that if I select a particular day, a pop-up box will appear over that cell that will contain the names of the people who are listed (further along) in that row as being Absent......any suggestions? Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need formula, please ? (excel 2003; Win XP)

    You could use cell comments. See the attached sample workbook (you must enable macros). There is code for the Worksheet_Change event in the worksheet module to update the comment when a cell in the range F2:AZ7 is changed (modify as needed).

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Need formula, please ? (excel 2003; Win XP)

    Thanks, Hans.....one question...how do I extend the 'code' or VBA to all of the dates (eg: your example stops at Jan 6, 2007.....if I extend the date range, how to I get the code to cover the full range so that each cell has a comment ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need formula, please ? (excel 2003; Win XP)

    The code refers to F2:AZ7 twice. You should edit both instances to include the columns that may contain names of absentees (I have assumed that there is no content to the right of the names), and to include the rows that are used. So for example, if your absentee names are in columns CA through CZ, and the dates are in A2 through A366, you should change F2:AZ7 to CA2:CZ366.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Need formula, please ? (excel 2003; Win XP)

    I already did what you suggested, Hans....but the new dates (eg: Jan 7 through Jan 11, for example) don't show the comment indicator (the red triangle).......and doing the usual "Insert Comment" doesn't work....

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need formula, please ? (excel 2003; Win XP)

    The event procedure will only change a comment if you add, edit or remove an absentee name. To initialize the comments, you can run the following macro (to be stored in a standard module) once:

    Public Sub InitializeComments()
    Dim intCol As Integer
    Dim lngRow As Long
    Dim strAbsentees As String
    For lngRow = 2 To 366 ' modify as needed
    strAbsentees = ""
    For intCol = 6 To Range("IV" & lngRow).End(xlToLeft).Column ' modify as needed
    strAbsentees = strAbsentees & ", " & Cells(lngRow, intCol)
    Next intCol
    If strAbsentees = "" Then
    strAbsentees = " "
    Else
    strAbsentees = Mid(strAbsentees, 3)
    End If
    If Cells(lngRow, 1).Comment Is Nothing Then
    Cells(lngRow, 1).AddComment Text:=strAbsentees
    Else
    Cells(lngRow, 1).Comment.Text strAbsentees
    End If
    Next lngRow
    End Sub

Posting Permissions

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