How to Dynamically Reference Another Excel Sheet from a Sheet Name
Question details
The user needs to automatically retrieve data from multiple worksheets into a summary sheet by referencing the sheet names listed in a column.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Building a summary sheet that aggregates data from multiple worksheets using hyperlinks and sheet names.
- Observed behavior
- Standard direct references remain static and continue to point to the originally referenced sheet when copied down rows, rather than updating dynamically based on the row's specified sheet name.
Ensure that the sheet names listed in your summary column exactly match the actual worksheet tab names, as any discrepancies or extra spaces will cause the reference to fail.
Use the INDIRECT Function for Dynamic Sheet Referencing
The INDIRECT function allows you to convert a text string into a valid cell reference, making it perfect for dynamically pulling data from different sheets based on a column of sheet names.
A standard reference like =Sheet2!A1 hardcodes the sheet name. By combining the INDIRECT function with string concatenation, you can substitute the hardcoded sheet name with a reference to a cell that contains the desired sheet name.
Locate the column in your summary sheet that contains the text names of the worksheets you want to pull data from (for example, Column C).
Select the cell where you want to display the retrieved data. Type the formula =INDIRECT("'"&$C2&"'!A9"), replacing $C2 with the cell containing your sheet name, and A9 with the target cell address on the referenced sheets.
Press Enter to calculate the result. Click and hold the fill handle at the bottom-right corner of the cell, then drag it down to apply the dynamic reference to the remaining rows in your summary table.
Easily Manage Dynamic References with WPS Spreadsheet
WPS Spreadsheet fully supports advanced text and reference functions like INDIRECT, making it incredibly simple to consolidate data across multiple worksheets. Build dynamic reports and dashboards seamlessly with an interface you already know.
- 1. Open your workbook: Launch WPS Spreadsheet and open the multi-sheet file you wish to consolidate.
- 2. Start the formula: In your summary sheet, select the target cell and type =INDIRECT( to begin the function.
- 3. Build the dynamic string: Click the cell containing your sheet name, then append the target cell text (e.g., &"'!A1") and close the parentheses.
- 4. Drag to fill: Press Enter, then use the bottom-right drag handle to quickly apply the dynamic formula to the rest of your summary sheet.
Frequently Asked Questions
Why does my INDIRECT formula return a #REF! error?
A #REF! error occurs when the text string does not form a valid cell reference. This usually happens if the sheet name referenced in the cell does not exist, contains a typo, or has hidden leading/trailing spaces. Verify that the text matches the actual tab name exactly.
Do I need to include single quotes around the sheet name in the formula?
Yes, if your worksheet names contain spaces or special characters, Excel and WPS Spreadsheet require the sheet name to be enclosed in single quotes. Your formula should look like =INDIRECT("'" & C2 & "'!A1") to ensure it parses correctly.
Can the INDIRECT function dynamically reference closed workbooks?
No, the INDIRECT function requires the referenced external workbook to be currently open. If you use INDIRECT to reference another workbook and that file is closed, the formula will result in a #REF! error.
