Formula to get sheet names

[mlocurci]

I have a workbook with many sheets, each sheet is a person name. Is there a way I can have a formula on my summary page enumerate the sheet names?

 

[AnotherHiggins]

You aren't going to want to hear this, but you should really, really, really change your workbook layout.

Like-data shouldn't be split apart by Name (or Part Number, Day, Week, Month, Year, etc.).

Instead, add a column for employee name. With everyone's data in a normalized master list, reporting for individuals will be very quick and simple. It will also be easy to pull data for a particular week (or month or quarter). You can look at data for the whole group, or be department or by employee.

Excel has a ton of built in reporting and analysis tools, but you need to have your data stored properly to use them.

For your current predicament, you'll need to use a macro (VBA) to get the list of all sheets in your workbook. Are you familiar with VBA?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

MEGA dittos to John's suggestion!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[buitenhek]

Have the following on each sheet and reference the summary sheet to that cell on each sheet:
=UPPER(MID(CELL("filename", A1), FIND("]", CELL("filename", A1))+1, 100))

Regards
Peter Buitenhek
ProfitDeveloper.com

 

[xlbo]

3rded to Skip & John and might I just say <cough>faq68-2561</cough>

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Salut39]

Zack Barresse kindly gave me this user defined function which I have been using ever since. Add this code to VBA module and you will find it under user defined functions.
Your formula will look like this:

=PERSONAL.XLS!Sheet(Row())

.. for the workbook the formula is entered in, change it to this ...


CODE
Public Function Sheet(wsIndex As Long) As String
Dim wb As Workbook
On Error Resume Next
Application.Volatile True
Set wb = Workbooks(Application.Caller.Parent.Parent.Name)
Sheet = "ERROR!"
Sheet = wb.Worksheets(wsIndex).Name
End Function

This will now handle an invalid sheet index number and use only the parent workbook.

Yuri