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]

Re: "Works great but there's no links to the worksheets."
If you put that 3 line code snippet in the worksheet module (right click on the sheet tab and select "view code") and go back to your sheet and select any sheet in column A, you should end up in that sheet by magic.
Only select a single cell in Column A. I have not included any error checking (yet).

 

[Quiet_Nectarine_]

I'm not a VBA expert but you could go around that using power query to extract a list of all the tabs in the excel workbook to form your content page. Then use formulas to add the hyper link later

 

[felixstraube]

Sorry for the delay.

Here is a working file to download:

IndexWorksheets.zip

If you need to add more sheets you could copy one of the existing sheets which already has the hyperlink back to the index, rename it.
Add the sheed name in column B, and you drag down the formula in column C.

Let me know if this works for you.

 

[jolivanes]

If you feel adventerous, you can have a UserForm with a Listbox and use these 2 snippets for the UserForm,

Private Sub ListBox1_Click()
Sheets(Left(ListBox1, InStr(ListBox1, "(") - 2)).Select
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim i As Long, dat
    For i = 2 To ThisWorkbook.Worksheets.Count    '<---- Starts with 2nd Sheet. Change as required.
        dat = dat & "|" & Sheets(i).Name & " (" & Sheets(i).Range("B6").Value & ")"
    Next i
dat = Split(Mid(dat, 2), "|")
ListBox1.List = Application.Transpose(dat)
End Sub

And copy the following into a regular module to open the UserForm.

Sub Show_Me()
    UserForm1.Show
End Sub

With this you don't need any data in a sheet. Just select in the ListBox to go to the selected sheet.

 

[jolivanes]

No reply from OP so I assume that all is well in his/hers excel world.

 

[jolivanes]

Not enough days for testing to see if everything works I guess.
You'd think that by now we should have seen a "Thanks guys, you saved me a lot of time." or something similar.