Link Multiple Excel Worksheets to Rows in a Summary Sheet
Question details
Collect the same cells from multiple worksheets into a summary sheet, with one row per source worksheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook has approximately 100 source worksheets, and the user wants to summarize selected cells without creating each link individually.
- Observed behavior
- The desired summary has a worksheet name in each row and values from specified source cells in adjacent columns.
Identify the source cell addresses to collect and confirm that the worksheets are in the same workbook. Create a sheet named Summary and reserve column A for worksheet names and row 1 for cell addresses.
Use INDIRECT to Fill Links Across the Summary Sheet
Use a worksheet-name list and cell-address headers to create reusable links without writing each reference separately.
On the Summary sheet, enter the source cell addresses in B1, C1, and subsequent headers, such as B2 and D5.
Enter or paste the exact source worksheet names in A2, A3, and the rows below, one name per row. Exclude the Summary sheet.
In Summary!B2, enter =INDIRECT("'"&SUBSTITUTE($A2,"'","''")&"'!"&B$1). This combines the sheet name in column A with the address in row 1.
Select B2 and drag its fill handle across the address columns, then down through all worksheet-name rows.

Generate the Worksheet List with an Excel VBA Macro
Use this option in desktop Excel when entering a long list of worksheet names would be inconvenient.
Link Worksheet Cells in WPS Spreadsheet
Use worksheet names, cell-address headers, and the INDIRECT function in WPS Spreadsheet to build a summary row for each source sheet.
- 1. Open the workbook: Open the Excel workbook in WPS Spreadsheet and add or select a Summary sheet.
- 2. Set up names and addresses: Enter source worksheet names in column A starting at A2. Enter the source cell addresses in B1, C1, and subsequent columns.
- 3. Enter the link formula: Select B2 and enter =INDIRECT("'"&SUBSTITUTE($A2,"'","''")&"'!"&B$1).
- 4. Fill and check the results: Drag the fill handle across and down, then check that each summary value matches its source worksheet.

Frequently Asked Questions
How do I summarize different source cells in separate columns?
Enter each source address in a separate header, such as B2 in Summary!B1 and D5 in Summary!C1. Fill the INDIRECT formula across so each column uses its own header.
Will the formula work with spaces or apostrophes in sheet names?
Yes. The formula surrounds the sheet name with single quotes and uses SUBSTITUTE to handle apostrophes within the name. Enter the exact sheet name in column A.
Why does my summary formula show #REF!?
Check that the sheet name in column A exists and that the header contains a valid cell address. If you reference another workbook with INDIRECT, make sure that workbook is open.
Does the worksheet list update automatically when I add a sheet?
No. Add the new sheet name to column A and fill the formulas into its row, or rerun the VBA list macro after clearing the old names below A1.




