How to Copy References Across Worksheets Without Changing Sheet Names in Excel
Question details
The user wants to automatically reference matching cells across multiple daily worksheets without manually updating the worksheet name in the formula for every column.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating or pulling data from multiple daily worksheets into a master summary sheet.
- Observed behavior
- Currently, the user has to manually change the worksheet name in the formula for each column, which is time-consuming and prone to errors.
Ensure that all your daily worksheets follow a consistent naming convention (such as sequential numbers or days of the week) and have identical data structures to prevent formula errors.
Use the INDIRECT and ADDRESS Functions
Reference sheet names dynamically by storing the sheet name in a header cell and converting it into a valid reference using the INDIRECT function.
By combining INDIRECT and ADDRESS, you can build a formula that reads the worksheet name from a cell in your current sheet. This allows you to drag the formula across rows or columns and pull data from different sheets automatically.
Determine the row and column numbers of the cell you want to reference from the daily sheets (e.g., row 10, column 9 represents cell I10).
Enter your target worksheet names into the header cells of your summary sheet. For example, type '1' in cell E1, '2' in cell F1, etc.
Select the cell where you want to output the data and enter the formula: =INDIRECT(ADDRESS(10,9,,,E1)).
Press Enter to calculate the result, then drag the fill handle across your columns so the formula dynamically pulls data using the different sheet names in your header row.
Use 3-D Ranges and Array Functions (Excel 365)
Define a 3-D range across multiple sheets and use modern array functions to consolidate the data quickly.
Dynamically Reference Worksheets with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like INDIRECT and ADDRESS, allowing you to easily consolidate data across multiple worksheets. It is highly compatible with Microsoft Excel formulas, ensuring a seamless data management experience without changing your workflow.
- 1. Open your workbook in WPS: Launch WPS Office and open your .xlsx file containing the multiple daily worksheets and your summary sheet.
- 2. Set up header cells: Type the exact names of your daily worksheets into the top row of your summary sheet to act as reference points.
- 3. Apply the INDIRECT formula: Type =INDIRECT(ADDRESS(row_num, col_num,,, header_cell)) and press Enter to pull the data dynamically, then drag to fill the rest of your columns.
Frequently Asked Questions
Why is my INDIRECT formula returning a #REF! error?
A #REF! error usually occurs if the sheet name referenced in the header cell does not match the actual worksheet name exactly, or if the referenced worksheet is in an external workbook that is currently closed.
Does the INDIRECT function work if the referenced worksheet name contains spaces?
Yes. If you use the ADDRESS function, it handles spaces automatically. If you construct the text manually without ADDRESS, you must enclose the sheet name in single quotes, like =INDIRECT("'" & E1 & "'!A1").
What is the difference between the ADDRESS and INDIRECT functions?
The ADDRESS function creates a text string representing a cell reference (such as '$I$10'), while the INDIRECT function takes that text string and converts it into a valid reference that the spreadsheet can actually calculate.
Can I use dynamic worksheet references in WPS Spreadsheet?
Absolutely. WPS Spreadsheet fully supports INDIRECT, ADDRESS, and 3-D references, allowing you to use the exact same formula logic to pull data across sheets as you would in Microsoft Excel.
