How to Dynamically Change Worksheet Names When Filling Excel Formulas
Question details
The user wants to copy a formula down a column and have it automatically reference different worksheet names to retrieve data from the same cell across multiple sheets.
- Product
- Spreadsheet/Excel
- Device & OS
- not provided
- Scenario
- Gathering data from hundreds of worksheets by retrieving a specific cell's value from each sheet sequentially without manually editing the sheet name in every row.
- Observed behavior
- Standard cell references do not increment or change worksheet names automatically when filling down formulas.
Ensure you have an exact list of all the worksheet names exactly as they appear in the workbook tabs to avoid reference errors when using dynamic formulas.
Use the INDIRECT Function for Dynamic Sheet References
The INDIRECT function lets you build dynamic cell references using text strings, making it perfect for referencing varying sheet names as you copy a formula down.
By listing your worksheet names in a column, you can combine them with a fixed cell reference using the INDIRECT function. When you drag the formula down, the sheet name reference updates automatically based on your list.
In a new column (e.g., Column A), type the exact names of your worksheets starting from cell A2 downwards.
In cell B2, enter =INDIRECT("'"&A2&"'!D4"), where A2 holds the sheet name and D4 is the specific cell you want to extract from each sheet.
Click the fill handle in the bottom-right corner of cell B2 and drag it down to apply the formula to the rest of the sheet names listed in Column A.
Gather Data Automatically with Dynamic Array Formulas
For advanced data consolidation across sheets, you can combine INDIRECT with dynamic array functions like REDUCE and VSTACK to stack ranges automatically.
Handle Complex Formulas Effortlessly with WPS Spreadsheet
WPS Spreadsheet offers full support for advanced functions like INDIRECT, VLOOKUP, and dynamic arrays. It provides a lightweight, fast, and completely free environment to manage massive datasets across multiple worksheets.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your multi-sheet workbook.
- 2. Create your list: In your summary sheet, create a list of your exact worksheet names in a single column.
- 3. Apply the formula: Type the =INDIRECT("'"&A2&"'!D4") formula in an adjacent cell to reference your target data.
- 4. Drag and fill: Drag the cell's fill handle down to dynamically retrieve data from all specified sheets instantly.
Frequently Asked Questions
Why does my INDIRECT formula return a #REF! error?
This usually happens if the worksheet name listed in your cell does not exactly match the actual tab name (e.g., there might be hidden trailing spaces), or if the referenced worksheet is in a different workbook that is currently closed.
Do I need to include single quotes around the sheet name in the INDIRECT formula?
Yes, including single quotes (like "'"&A2&"'!") is highly recommended. It ensures the formula calculates correctly even if your worksheet names contain spaces or special characters.
Can I use INDIRECT to reference varying cell addresses instead of varying sheet names?
Absolutely. The INDIRECT function converts any valid text string into a reference. You can combine row and column text strings dynamically, such as =INDIRECT("A"&B1) to reference column A and the row number specified in cell B1.
