Fix Excel 2016 GET.WORKBOOK Formula Returning Only One Worksheet
Question details
The user is attempting to list all worksheet names using the GET.WORKBOOK legacy macro function, but the formula only outputs a single worksheet name instead of the complete list.
- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- Creating a dynamic index or list of all worksheet names within a workbook using formulas.
- Observed behavior
- The formula returns only the first or last worksheet name because Excel 2016 does not support modern dynamic arrays, preventing the function from automatically spilling the multiple results into adjacent cells.
Ensure your document is saved as an Excel Macro-Enabled Workbook (.xlsm) and that macros are enabled in your Trust Center settings, as GET.WORKBOOK is an Excel 4.0 macro function that will not run in standard .xlsx files.
Use a Legacy Array Formula (Ctrl+Shift+Enter)
Since Excel 2016 lacks dynamic array capabilities, you must manually allocate a range of cells and confirm the formula as an array to force it to display all worksheet names.
In newer versions of Office (like Microsoft 365), array functions automatically 'spill' into neighboring cells. In Excel 2016, you have to explicitly tell the software to treat your entry as a legacy array formula over a pre-selected range.
Navigate to Formulas > Name Manager and click New. Enter a name like 'SheetNames' and in the 'Refers to' box, type =GET.WORKBOOK(1) and click OK.
Highlight a range of cells where you want the worksheet names to appear (for example, select cells A1 through K1 for a horizontal list).
With the range still selected, click into the formula bar and type the formula referring to your defined name, such as =SheetNames.
Instead of pressing Enter, press Ctrl + Shift + Enter simultaneously. Excel will enclose your formula in curly braces {}, and the worksheet names will populate across the selected cells.
Extract Specific Sheet Names Using the INDEX Function
Wrap the defined macro name inside an INDEX function to pull individual worksheet names one by one using row numbers.
Generate the Worksheet List Using VBA
Bypass the limitations of Excel 4.0 macro functions entirely by using a simple VBA script to generate the list of sheet names.
Upgrade to WPS Office for Seamless Spreadsheet Management
Struggling with legacy array limitations in older versions of Excel? WPS Office provides a free, modern, and lightweight spreadsheet solution with excellent compatibility for advanced formulas, VBA macros, and modern array functions.
- 1. Download and Install: Visit the official WPS website to download and install the free WPS Office suite on your device.
- 2. Launch WPS Spreadsheet: Open WPS Office and select the Spreadsheet module from the main dashboard.
- 3. Open Your Workbook: Drag and drop your existing Excel file into the workspace. Your macros, defined names, and formulas will carry over smoothly.
Frequently Asked Questions
Why does GET.WORKBOOK return an error if I type it directly into a cell?
GET.WORKBOOK is an Excel 4.0 macro function, not a standard worksheet function. It cannot be typed directly into a cell; it must be executed by first defining it in the Name Manager and then calling that defined name in your worksheet.
How do I remove the workbook name from the GET.WORKBOOK result?
By default, GET.WORKBOOK returns the name in the format '[Workbook.xlsm]Sheet1'. You can strip the workbook name using text functions. For example: =REPLACE(SheetNames, 1, FIND("]", SheetNames), "") will leave only the sheet name.
Does GET.WORKBOOK update automatically when a new sheet is added?
No, it does not update dynamically because it is not a volatile function. You can force it to recalculate by adding a volatile trigger to your formula in the Name Manager, such as appending &T(NOW()) to the end of your GET.WORKBOOK string.
Why does my GET.WORKBOOK formula show a #BLOCKED! error?
This error occurs because macro execution is restricted. To fix it, you must go to File > Options > Trust Center > Trust Center Settings > Macro Settings, enable VBA macros, and ensure your file is saved in the .xlsm format.
