How to Copy Cells from Multiple Excel Sheets with One Formula
Question details
The user needs to extract data from the exact same cell reference across multiple worksheets without having to manually update the sheet name in the formula for every single row.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Aggregating data from the exact same cell reference across multiple different worksheets into a single master summary sheet.
- Observed behavior
- When copying a standard formula down a column, the row or column references change dynamically, but the worksheet name remains static, requiring tedious manual edits for each sheet.
Ensure all the worksheets you want to reference are exactly named as they appear in your summary list. Any spelling mistakes or extra spaces in the sheet names will result in a #REF! error.
Use the INDIRECT Function to Reference Multiple Sheets Dynamically
This is the most efficient way to pull data from multiple sheets by combining the INDIRECT function with a typed list of your worksheet names.
The INDIRECT function converts a text string into a valid cell reference. By listing your sheet names in a column and referencing them within the INDIRECT formula, you can drag the formula down to instantly pull data from multiple sheets without manual editing.
In your summary worksheet, list the exact names of the target worksheets in a single column (for example, type the sheet names in cells D4, D5, D6, etc.).
Click on the cell where you want the data to appear (e.g., E4). Type the formula =INDIRECT("'"&D4&"'!I9") and press Enter. In this example, D4 is the cell containing the sheet name, and I9 is the specific cell you want to extract from that sheet.
Select cell E4, click and hold the small square at the bottom-right corner of the cell (the fill handle), and drag it down the column to apply the formula to the rest of your sheet names.
Easily Manage Multiple Worksheets with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like INDIRECT, allowing you to seamlessly aggregate and manage data from multiple sheets with high performance and zero cost.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the multiple sheets.
- 2. List Your Sheet Names: Create a summary sheet and list all the worksheet names in a dedicated column.
- 3. Apply the INDIRECT Formula: Use the =INDIRECT formula referencing your list to instantly extract the target cell data from all sheets.
Frequently Asked Questions
Why is my INDIRECT formula returning a #REF! error?
This typically happens if the worksheet name in your typed list does not perfectly match the actual tab name (watch out for trailing spaces), or if the referenced worksheet has been deleted or renamed.
Can I use this method if the other sheets are in a different workbook?
Yes, but with a limitation: the INDIRECT function only works with external workbooks if that external workbook is currently open on your computer. If the external file is closed, the formula will return a #REF! error.
Is there a way to sum the same cell across multiple sheets without INDIRECT?
Yes. If you simply want to add the values of the same cell across multiple consecutive sheets, you can use a 3D reference formula like =SUM(Sheet1:Sheet5!A1). This adds the value in cell A1 from Sheet1 through Sheet5.
