List worksheet names using a formula

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.

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

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.

Thanks, Tony. Food for thought!

You're welcome!

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

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

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

@ vlady...

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

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

Administrative Note:

Welcome to the forum.

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