|re: I know its a pain trying to imagine these things|
We're getting close!
1 - I assume that when you say "make a comment, put it in the cell" you actually mean a Comment, such as what you would normally add via Insert...Comment in the spreadsheet. Is that correct? You don't mean that you simply want the Name to appear in a cell, as a piece of data...you really do want a Comment, right?
2 - I'm not sure how the Department column enters into this. Is it somehow related to where you want the Comment?
In other words, I guess I'm trying to find out if you want the Comment added directly to the cell that, for example, contains Week1 under January. Is that what you want?
Assuming my assumptions are right, try this code in the Sheet2 module.
Note: The validation lists for the Months and Weeks must match the contents of the Month and Week cells on Sheet1. Since the code uses the Find method, it will only find an exact match.
I suggest that you create your validation lists directly from the cells on Sheet1 so that there is no chance that the lists won't match the cell contents.
Let me know what you think....
Private Sub Worksheet_Change(ByVal Target As Range)
Dim theWeek, theMonth As String
Dim m, w As Range
On Error Resume Next
'Was Changed Made to Column 6 (F)?
If Target.Column = 6 Then
'If yes, grab Week and Month
theWeek = Target.Value
theMonth = Target.Offset(0, -1)
'Find Month in Sheet 1, Row 1
Set m = .Find(theMonth, _
'Find Week for that month
With Sheets(1).Range(Sheets(1).Cells(2, m.Column), _
Sheets(1).Cells(2, m.Column + 3))
Set w = .Find(theWeek, _
'Add comment to cell on Sheet1 using data from Sheet2!A(same row)