List Sheet Names Without Macro or Excel 4 Functions

Question

I am trying to list all sheets in an Excel workbook with a method that works for macro-free workbooks such as .xlsx files.

I am aware of the following options although both require the workbook to be saved in a file format that allows macros:

Method 1: Excel 4 Function

See this answer I posted.

Method 2: VBA

See this answer posted by another user.

Is there any option to list all sheets? If not is there any formula that names any sheet beyond the sheet containing the formula?

Answer 1

If you have the flexibility, you can use the formula from @urdearboy in e.g. A1 on each sheet, then use a 3D reference to collect them together e.g. =TEXTJOIN(CHAR(10), FALSE,'FirstSheet:LastSheet'!A1) and then extract from the string.

It's a big kludge but it does work. (But at the moment I'm happy with all the sheets listed in 1 cell; A1 contains sheetname + description)

Caveat - FirstSheet & LastSheet must obviously span the range of sheets to be listed, and if they get moved around, the 3D reference may be inappropriate or break

Answer 2

This is a great question in the context of building macro-free workbooks.

To make an contents list of sheetnames, the formula from @urdearboy can be pasted in cell A1 of each sheet being indexed:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

If cell A1 of each of these sheets is made a named range, using foo_1, foo_2, foo_3 etc as the names, the index is simply =foo_1 in the first cell of the contents range, or better =indirect("foo_", 1), with 1 replaced by a cell reference to a number sequence.

The content list stays up-to-date, even if the sheet names are changed.