Search code examples
excelexcel-formulaexcel-4.0

List Sheet Names Without Macro or Excel 4 Functions

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?

Solution

  • 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