Excel Gurus-Please Help!

[Guest]

I have included a link to a workbook in which the master worksheet contains a
column with a list of tasks, and then has 2 columns for each month (one for
the due date and one for the date completed).
The following worksheets are for various departments. They each have
different tasks, but all of the tasks can be found on the master worksheet.
What I need is some way to have the “date completed†cells on the master
sheet automatically entered once that task has been completed on all of the
worksheets that contain that task.
I inherited this workbook that is used to track the compliance of various
departments throughout our site, so it is likely to have additions. It can
be reformatted as necessary.
THANKS SO MUCH!!

http://www.mediafire.com/?cvlymtxdjlg

 

[iliace]

I'm not guru (at least I won't claim that in these newsgroups) but
I'll give it a shot. This can get tricky, but VBA is probably the
route for you (unless someone has better solutions). Here's something
I put together that works, based on the current arrangement of the
worksheet. If a date is entered in the column whose second row reads
"Completed", it looks up the task name in the second column, finds
that row on the master worksheet, looks for the due date from the
department sub-sheet, and populates the completed date on the master.
You will probably want to do some more error checking; this snippet
only accounts for two condition: task not in master worksheet, or due
date doesn't match. Try it out and post back. It goes into the code
module of each department worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.EntireColumn.Cells(2, 1).Value = "Completed") Then
Application.EnableEvents = False
Application.ScreenUpdating = False

Dim wshMaster As Excel.Worksheet
Dim strTaskName As String
Dim iMasterRow As Long
Dim iMasterColumn As Long

Dim dtDeadline As Date

strTaskName = Target.EntireRow.Cells(1, 2).Value
dtDeadline = Target.Offset(0, -1).Value

Set wshMaster = ThisWorkbook.Worksheets("Master")

With wshMaster
On Error Resume Next
iMasterRow = .Cells(1, 2).EntireColumn.Find(strTaskName).Row
On Error GoTo 0

If iMasterRow = 0 Then
Call MsgBox("Task not found in master worksheet!", _
vbOKOnly + vbExclamation, "Task not found")
GoTo exitCode
End If

On Error Resume Next
iMasterColumn = .Cells(iMasterRow,
1).EntireRow.Find(dtDeadline).Column
On Error GoTo 0

If iMasterColumn = 0 Then
Call MsgBox("Due date not found on master worksheet!", _
vbOKOnly + vbExclamation, "Due date not found")
GoTo exitCode
End If
.Cells(iMasterRow, iMasterColumn + 1).Value = Target.Value
End With
End If

exitCode:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub