Drop Down Box to Display range

[VanessaY]

I have timesheets for 20 member of staff listed in forms one below another on
one sheet which they fill in weekly. I would like to have a drop down box at
the top of the sheet which the member of staff clicks on and then it displays
the one relevant timesheet rather than them having to scroll down. How do I
do this?

 

[Bob Phillips]

Why not have a separate sheet for each staff member, named after them, easy
to find.

 

[Patrick Molloy]

have a table of your staff members names. then use a hyperlink on each name
to take them to the relevent timesheet

 

[Gord Dibben]

If you need the 20 forms on one sheet try this.

In L1 create a DV dropdown with the source being a list of your 20 staff
members.

Freeze row 1 so's the DV dropdown is always accessible.

Copy this event code to your sheet module. Right-click on sheet tab and
"View Code" then paste.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$L$1" And Target.Value <> "" Then
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Columns("A")
Set c = .Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole, _
MatchCase:=False)
ActiveWindow.ScrollRow = c.Row
End With
End If
stoppit:
Application.EnableEvents = True
End Sub

Assumes column A is where your member names would reside.........edit to
suit.


Gord Dibben MS Excel MVP