# List worksheet names using a formula

This is a discussion on List worksheet names using a formula within the Excel Questions forums, part of the Question Forums category; I don't know if this topic has been posted before (I didn't do a search!) but if anyone's interested here's ...

1. ## List worksheet names using a formula

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)

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

2. ## Re: List worksheet names using a formula

Ooops!

=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"))

3. ## Re: List worksheet names using a formula

It is Great Biff
really it is amazing way
but u say
This uses an old macro function.

4. ## Re: List worksheet names using a formula

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.

5. ## Re: List worksheet names using a formula

Originally Posted by 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)

=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))

6. ## Re: List worksheet names using a formula

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

7. ## Re: List worksheet names using a formula

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

8. ## Re: List worksheet names using a formula

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

regards
David