Building an Index of worksheets

[SandsB]

I have an Excel file with about 75 worksheets. Each of those worksheets is assigned to 1 of a few people (it fluctuates). It's a pain to scroll all over the place looking for worksheets assigned to a specific person. Way back when, someone here helped with something that updated a sort of Table of Contents page that listed each worksheet and included a link to it - click the name and it would take you to that worksheet. I put that to good use but I'm in a different job now and lost track of that file. What that didn't do and what I'm looking for now is the same thing but in one column show the name of the worksheet and in another show the person assigned. That person is always listed in cell B6 in each worksheet. Doable?

Worksheet1 Name Person Assigned Name
Worksheet2 Name Person Assigned Name
Worksheet3 Name Person Assigned Name
Worksheet4 Name Person Assigned Name
etc

 

[jolivanes]

If you're going to have everything in the first sheet and the first sheet is part of the data gathering, it will overwrite the name in B6.
Start with the 2nd Sheet?

 

[Jeffrey Mahoney]

Another option is to add each person's user login name on the sheets, along with their names. You can use that to automatically go to the correct sheet without having to maintain a table of contents. You could either navigate to the correct sheet when they open the workbook, or simply have a single button to navigate automatically to their sheet. A macro could be created that simply searches each sheet for the login name.

This line can get the user's login name

CurUser = UCase(Environ("UserName"))

 

[jolivanes]

Looks like Jeffrey Mahoney's solution is the simplest.

Try so.
In a regular Module (Module1?)

Sub Get_Sheet_Names()
Dim i As Long
    For i = 2 To ThisWorkbook.Worksheets.Count
        With Cells(i, 1)
            .Value = Worksheets(i).Name
            .Offset(, 1).Value = Worksheets(i).Range("B6").Value
        End With
    Next i
End Sub

In the Sheet Module where the Sheet Names/People Names are

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 And Len(Target) <> 0 Then Sheets(Target.Value).Select
End Sub

 

[Jeffrey Mahoney]

Apply this to a button or add to THISWORKBOOK module using "Private Sub Workbook_Open()"

Sub NavigateToUserSheet()
  Dim WS As Worksheet
  Dim LoginName As String
  
  LoginName = UCase(Environ("UserName"))
  
  For Each WS In ThisWorkbook.Worksheets
    Debug.Print WS.Name
    If InStr(WS.Range("B6").Value, LoginName) > 0 Then
      WS.Activate
      Exit For
    End If
  Next WS
  
End Sub

 

[felixstraube]

Maybe something like this will work for you?:

(this will only work with the desktop version of excel)

IndexWorksheets.xlsx
	A	B	C
1	Name	Worksheet	Hyperlink to sheet
2	John Connor	Worksheet 1	go…
3	Marty McFly	Worksheet 2	go…
Index

Cell Formulas
Range	Formula
C2:C3	C2	=LET(fp,CELL("filename"), filename, TEXTBEFORE(TEXTAFTER(fp,"["),"]"), HYPERLINK("["&filename&"]'"&B2&"'!A1", "go…") )

Text in worksheet column hast to be exactly the name of each sheet.

And you could add a hyprelink back to the Index Sheet like this:

Don't know if this is what you need.

 

[SandsB]

I think there's a combination of me not explaining well enough and me not skilled enough to understand some of the responses. I added jolivanes' code and it give me a perfect index showing the name of the worksheet and the name of the person. Works great but there's no links to the worksheets. The image in felixstraube's response with the "go..." links looks good but I don't understand the code. The text in column A linking to the appropriate worksheet would also work.
(Incidentally, one person could be assigned multiple worksheets).

 

[Jeffrey Mahoney]

So with the code I provided, all you need to do is put each person's login name in each of the worksheets they are assigned. Cell B6 could have multiple names/login names.

 

[SandsB]

Jeffrey, trying to understand your code. I'm not one of you Excel gurus. Does it open a worksheet for the user logged in? I'm looking for an index - users will have multiple worksheets but still need to see some assigned to others.

 

[Jeffrey Mahoney]

It does take the user to a worksheet. Add a button to any sheet and link it to that macro you pasted into a standard module. Since you are testing it, go put your login name on any of the sheets in cell B6. Now, go click the button.

If a user doesn't know their login name use this

Sub DispalyLoginName()
  MsgBox UCase(Environ("UserName"))
End Sub