Formula to lookup worksheet label

[Grotejm]

Background:
I have a workbook that has 250 plus tabs in it (I know - it's crazy). The
workbook is generated by exporting a report from our financial system so the
tab names change on a regular basis.

Question:
Does anyone know of a way to get a list of worksheet labels related a
workbook? Either via a cut and paste or via a function?

My hope is to be able to insert a worksheet at the beginning of the file and
then via the hyperlink function create a list of 250+ hyperlinks to the
various worksheets. I have the basic formula but am stuck trying to figure
out a way to have the formula pull the various worksheet labels. The nested
formula would need to know what tab to goto without me typing in the tab
name. I think what I need is similar to how the "offset" function works but
instead of telling it how many rows or columns to move I need to tell how
many worksheets (tabs) to move through.

Any ideas?
Judy

 

[Air_Cooled_Nut]

I don't know of a way that a formula could do this, however, I have created a
TOC like you describe using VBA coding.

 

[GoBow777]

Background:
I have a workbook that has 250 plus tabs in it (I know - it's crazy).
The
workbook is generated by exporting a report from our financial system
so the
tab names change on a regular basis.

Question:
Does anyone know of a way to get a list of worksheet labels related a
workbook? Either via a cut and paste or via a function?

My hope is to be able to insert a worksheet at the beginning of the
file and
then via the hyperlink function create a list of 250+ hyperlinks to the

various worksheets. I have the basic formula but am stuck trying to
figure
out a way to have the formula pull the various worksheet labels. The
nested
formula would need to know what tab to goto without me typing in the
tab
name. I think what I need is similar to how the "offset" function
works but
instead of telling it how many rows or columns to move I need to tell
how
many worksheets (tabs) to move through.

Any ideas?
Judy

Hello Judy:

This may help.

Scroll down to Miscellaneous
http://www.cpearson.com/excel/excelF.htm#SheetName

http://www.cpearson.com/excel/excelM.htm#SheetNames

 

[Gord Dibben]

You can start by placing all the sheet names in a list in a new worksheet.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "List"
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub


Gord Dibben MS Excel MVP