How to sort info from separate worksheets on summary sheet

[USNRet]

Hi,
My workbook contains 15 worksheets. I track employee work hours an
amount of money spent each payperiod by my spreadsheet for severa
projects.

The first worksheet is my summary worksheet which lists each employe
by name and cumulative work hours from the additional worksheets. Th
additional worksheets are specific work tasks and lists employees an
work hours related to that task.

Is there any way to click on an individual name in the Summar
Worksheet and display that employees allocated hours for each workshee
(task)?

Thank you

 

[jeff]

Hi,

I'm not sure if this is what you might be looking for,
but the following code will show in a messagebox the
individual amounts for a name. It assumes your
names are unique and in cells A1:A4, that those names
also match exactly, show up once, and are on the
additional sheets in cells A1:A5 and their hours are
in the corresponding B cell. You might have to play
with other things to fine tune it.


Sub showHrs(ByVal Target As Range)
Dim Nm, msgg As String
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1:a4")) Is Nothing Then
Exit Sub
Nm = Target
For j = 1 To Worksheets.Count
If Worksheets(j).Name <> "Sheet1" Then
With Worksheets(j).Range("A1:A5")
Set c = .Find(Nm, LookIn:=xlValues)
If Not c Is Nothing Then
vl = c.Offset(0, 1).Value
End If
task = Worksheets(j).Name
End With
If msgg = "" Then msgg = Nm
msgg = msgg & vbCrLf & task & "-" & vl
End If
Next j
MsgBox msgg
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
showHrs Target
End Sub

Hope this helps.
jeff