Thread: Hide a list's contents and locate the list results

Evening all.

I have a (very large) workbook that currently has me stumped. I have attached a basic copy of the file. Had to make it basic to remove all personal information and because of the sheer amount of formulas currently in use on the workbook.

Basically on the 'Home Page' the numbers are a direct cell reference from another sheet. This means when I add new colleagues to their shift sheet, they automatically appear in the list.In total there is 1 main sheet and 5 shift sheets.



I am wondering if it is possible to select a line using the list and then be sent to that cell in the workbook. For instance, If I select 1 in the Demo workbook attached using the list, I can click a button and I will be sent to cell A7-B7 on sheet Shift 2.

Also, Is there a way to make the contents of the list hidden until someone uses the list? I don't want the whole list of 300+ names displayed, only the name of the selected colleague.



Does it make sense and does anyone have any ideas?
Thanks very much

Seb

Originally Posted by mrice

Try this macro to do the navigation.

Sub Test()
Dim Sheet As Worksheet
Dim FirstName As String
Dim LastName As String
Dim N As Long

Sheets("Home page").Activate
If Selection.Rows.Count > 1 Then
    MsgBox "Only one row should be selected"
    Exit Sub
End If
FirstName = Cells(Selection.Row, 1)
LastName = Cells(Selection.Row, 2)
For Each Sheet In ThisWorkbook.Sheets
    If Sheet.Name <> "Home page" Then
        For N = 7 To Sheet.Cells(Sheet.Rows.Count, 1).End(xlUp).Row
            If Sheet.Cells(N, 1) = FirstName And Sheet.Cells(N, 2) = LastName Then
                Sheet.Activate
                Sheet.Range(Cells(N, 1), Cells(N, 2)).Select
                Exit Sub
            End If
        Next N
    End If
Next Sheet
End Sub

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.


Not sure that I understood the bit about hiding the lists.

Works like a charm in the demo test page.
As long as First Name and Surname stay in Columns A and B respectively it works without fail

Thanks

1 last question. I have a second nearly identical workbook but the list starts in D15 and E15. Which part of this code would I need to change for it to work there?

Thanks again

I now have a decent search function working on both Workbooks.
Saves manually looking through 300+ colleagues on 5 Sheets, soon to be 10,000 on about 15 sheets.

Thanks so much for your help. Will save a lot of time and effort for all.