# Off Topic > Tips and Tutorials >  >  List worksheet names using a formula

## Tony Valko

I don't know if this topic has already been introduced. I tried using the search utility but all it did was "complain" about the search parameters I used.

You can generate a list of the worksheet names using formulas. This method uses an XL4 macro function combined with worksheet functions.

Here's how to do it:

Create this defined name...

In Excel versions 2007 and later:

Goto the Formulas tab>Define Name

In Excel versions 2003 and earlier:

Goto the menu Insert>Name>Define

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

Then, to list the sheet names enter one of these formulas in cell A1:

In Excel versions 2007 and later:

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

In Excel versions 2003 and earlier:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1))))

Copy down until you get blanks.

Some notes:

The &T(NOW()) is used to make the formula volatile so that it recalculates when a sheet is added/deleted or 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.

If doing this in Excel versions 2007 and later the file must be saved as a macro enabled file in the *.xlsm file format.

----------


## AliGW

Brilliant! Is it possible to tweak the formula to omit hidden sheets and/or omit, say, first and last from the generated list?

----------


## Tony Valko

Unfortunately, using this technique, you can not omit hidden sheets.

You can specify which sheet name you do want by modifying this expression:

ROWS(A$1:A1)

As you drag copy the formula the ROWS function is used as an incrementer to tell the INDEX function which sheet name to return. The sheet names are "stored" in the INDEX function in "positions" starting from position 1 to position N (the total number of sheet names).

Such that:

ROWS(A$1:A1) = sheet1
ROWS(A$1:A2) = sheet2
ROWS(A$1:A3) = sheet3
etc
etc

So, if you want to start the list with sheet2 then change ROWS(A$1:A1) to ROWS(A$1:A2).

We could probably put something in the formula to omit the first and last sheet name by comparing the number of rows the formula is being copied to against the total number of sheets.

----------


## AliGW

Thanks, Tony. Food for thought!  :Smilie:

----------


## Tony Valko

You're welcome!  :Cool:

----------


## bobjm

Excel 2007, did not work for me
get.workbook(1) is not a valid function

----------


## Tony Valko

You have to create the defined named formula "SheetNames".

Here's a small sample file created in Excel 2007 that demonstrates this.

List Sheet Names Excel 2007.xlsm

----------


## vlady

also this one.

name: listworkbooks
refers to=FILES(A1).

in cell A1 you can enter a path /folder / filename or wild cards like c:\*.*    -> c:\month*.xls

=INDEX(listworkbooks,ROW()) -> start it in row 1 any column

----------


## bobjm

thx Tony
must have had a typo
what I was hoping for is Workbook-A getting the worksheet names in Workbook-B

----------


## Tony Valko

You could use the method in one workbook then just use simple link formulas in the other workbook.

----------


## Tony Valko

@ vlady...

I have a macro that does pretty much the same thing but I just tried your suggestion and it works just fine.

----------


## Keith.Hejnal

This is great however it only works dragging down in a column for the list, is it possible to make it drag across so the list is in a row instead of a column?

----------


## FDibbins

> This is great however it only works dragging down in a column for the list, is it possible to make it drag across so the list is in a row instead of a column?



*Administrative Note:*

Welcome to the forum.  :Smilie: 

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

----------

