List worksheet names using a formula

# Thread: List worksheet names using a formula

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

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

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.

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

Originally Posted by MyExcel
It is Great Biff
really it is amazing way
but u say

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•