List worksheet names using a formula

[T. Valko]

I don't know if this topic has been posted before (I didn't do a search!) but if anyone's interested here's a formula method to list worksheet names.

This uses an old macro function.

Create this defined name:

Insert>Name>Define
Name: SheetNames
Refers to: =GET.WORKBOOK(1)&T(NOW())
OK

Then, to list the sheet names, entered in cell A1:

=INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1))

Copy down until you get errors. Use an error trap if you'd like!

Some notes:

The &T(NOW()) is used to make the formula volatile so that it calculates when a sheet name is changed.

GET.WORKBOOK(1) returns the sheet names as a horizontal array.

1 is the argument index number for returning the sheet names.

Some examples of other uses:

Return the sheet name of the nth sheet (n=2):

=INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),2)

How many sheet names start with the word Sheet:

=SUMPRODUCT(--(LEFT(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),0),4)="Sheet"))

 

[T. Valko]

Ooops!

How many sheet names start with the word Sheet:

=SUMPRODUCT(--(LEFT(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),0),4)="Sheet"))

"Sheet" has 5 characters so:

=SUMPRODUCT(--(LEFT(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),0),5)="Sheet"))

 

[MyExcel]

It is Great Biff
really it is amazing way
but u say

This uses an old macro function.

how i can get more examples and more Explains about This " old macro function."

 

[T. Valko]

You can download the macro function help files here:

http://support.microsoft.com/kb/128185

It will extract 3 files:

Macrofun.cnt
Macrofun.gid
Macrofun.hlp

Once extracted, just double click on Macrofun.hlp.

 

[JiaChiao]

I don't know if this topic has been posted before (I didn't do a search!) but if anyone's interested here's a formula method to list worksheet names.

This uses an old macro function.

Create this defined name:

Insert>Name>Define
Name: SheetNames
Refers to: =GET.WORKBOOK(1)&T(NOW())
OK

Then, to list the sheet names, entered in cell A1:

=INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1))

Copy down until you get errors. Use an error trap if you'd like!

Some notes:

The &T(NOW()) is used to make the formula volatile so that it calculates when a sheet name is changed.

GET.WORKBOOK(1) returns the sheet names as a horizontal array.

1 is the argument index number for returning the sheet names.

Some examples of other uses:

Return the sheet name of the nth sheet (n=2):

=INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),2)

How many sheet names start with the word Sheet:

=SUMPRODUCT(--(LEFT(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),0),4)="Sheet"))

This method may return wrong information if there are more than one workbook opened.

I wonder if that will be better to add two more steps:

1. Enter formula "=CELL("filename",1:1048576)" in a defined cell, here I use Sheet2!$A$1

2. Enter formula "=MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1)" in next cell, here I use Sheet2!$B$1

3. Insert>Name>Define
Name: SheetNames
Refers to: =GET.WORKBOOK(1,'Sheet2'!$B$1)&T(NOW())

4. Then, to list the sheet names, entered in cell A1:

=INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1))

 

[waqaszhr]

can you plz explain step three?? i can understant how to execute it .

 

[sobuj53]

Great work, but I was wondering if you could help me to use this formula to list all sheets that starts with Sheet instead of counting them! Thanks in advance

 

[davidjohnson]

This rocks! I can now extract data from the same cell of every sheet, something I have struggled with for ages

regards
David

 

[Marcelo Branco]

Hi T Valko

This is an old thread with a nice and very useful formula.

But i cannot figure out the role of
&T(NOW())
in the defined name.

For me with worked with or without this bit.

Am i missing something?

M.

 

[jimedmunds]

It is Great Biff
really it is amazing way
but u say

how i can get more examples and more Explains about This " old macro function."

I have come across a big issue (for my application) with this method - when i try to save the file as .xlsx format i get a message that

"The following features cannot be saved in macro-free workbooks: Excel 4 functions stored in defined names".

I need to save this as a macro-free workbook so does anybody have an alternative macro-free method for listing all the tabs in a workbook...?

Thanks