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.
Bookmarks