Expert: Bob Umlas - 4/28/2008

Question
Hi, I'm trying to set up a call-log with a master sheet of each individual call, and individual sheets for each person called. My master sheet looks like this:

DATE  TIME   NAME   REASON FOR CALL   RESULT

And my individual sheets look like this (note that I'm not using a NAME field for the individual sheets, but rather naming each worksheet the name of the person):

DATE   TIME   REASON FOR CALL   RESULT

Is there a way for me to update in the individual worksheets and have the data also appear on the master list? It would also be helpful if on the master list it could import the NAME field from the name of each worksheet, without me having to add a field to the individual sheets.

Thanks in advance for your help!

Answer
This requires the master sheet to be named MASTER:

Right-click the Excel LOGO (Near the file menu), select View Code, paste this in:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   If Sh.Name = "Master" Then Exit Sub
   If Target.Column > 2 Then n = Target.Column + 1 Else n = Target.Column
   rw = Sheets("Master").Cells(65500, n).End(xlUp).Offset(1).Row
   Sheets("Master").Cells(65500, n).End(xlUp).Offset(1).Value = Target.Value
   Sheets("Master").Cells(rw, 3).Value = Sh.Name
End Sub

HTH