copy data from a range in multiple sheet to master sheet

Hi,

I have searched in almost all the forums, but i have not been able to achieve what i need. I have to create a work sheet for my manager where he will assign different task/projects to us, and the same would be a updated in a shared file kept in our common DIR thus whenever we open we can view our individual sheets view the task assigned and update accordingly.

Now, i have already created a Master sheet where task assigned gets populated on individual sheet but i am stuck at is how get the master sheet updated for column (Status) & column(update) when we update our sheets in column (F) & column (G) the same needs to be updated on master sheet ?

Formula/macro anything is it possible?

Attach the following code to a update on the Master worksheet. You cannot use vllokup on this problem since it will cause an almost circular reference and the individual worksheets will loose their data.

' update the status and update fields for each master task
Private Sub Update_Click()

    Dim sh As Worksheet
    Dim i As Long
    Dim Found As Boolean
    Dim rw As Range
    Dim theRows As Range

    ' get the rows of the master sheet and loop through them
    Set theRows = Me.Range("2:" & CStr(Me.UsedRange.Rows.Count))
    For Each rw In theRows.Rows

        ' exit if the assigned to value is blank
        If (rw.Cells(1, 2).Value = "") Then Exit For
        Found = False

        ' get the reference worksheet.
        On Error Resume Next
        Set sh = Worksheets(rw.Cells(1, 2).Value)
        If (Err.Number = 0) Then

            ' find the taks assigned
            For i = 4 To sh.UsedRange.Rows.Count
                If (sh.Cells(i, 2).Value = rw.Cells(1, 3).Value) Then

                    ' extract the Status and Update fields
                    rw.Cells(1, 7).Value = sh.Cells(i, 6).Value
                    rw.Cells(1, 8).Value = sh.Cells(i, 7).Value
                    Found = True
                    Exit For
                End If
            Next i
        End If
        On Error GoTo 0

        ' if Assigned To or Task was not found, write ???
        If (Not Found) Then
            rw.Cells(1, 7).Value = "???"
            rw.Cells(1, 8).Value = "???"
        End If
    Next rw

End Sub