Function Problems

Link Multiple Excel Worksheets to Rows in a Summary Sheet

Huda QurayshiHuda Qurayshi Nov 18, 2026 869 views

Question details

Collect the same cells from multiple worksheets into a summary sheet, with one row per source worksheet.

Link Multiple Excel Worksheets to Rows in a Summary Sheet
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the source addresses

On the Summary sheet, enter the source cell addresses in B1, C1, and subsequent headers, such as B2 and D5.

2
List the worksheets

Enter or paste the exact source worksheet names in A2, A3, and the rows below, one name per row. Exclude the Summary sheet.

3
Enter the formula

In Summary!B2, enter =INDIRECT("'"&SUBSTITUTE($A2,"'","''")&"'!"&B$1). This combines the sheet name in column A with the address in row 1.

4
Fill the summary

Select B2 and drag its fill handle across the address columns, then down through all worksheet-name rows.

Use INDIRECT to Fill Links Across the Summary Sheet
Check reference errors: If a cell shows #REF!, check that the worksheet name in column A and the cell address in row 1 are valid. INDIRECT links to external workbooks require those workbooks to be open.
Summarize worksheets in WPS

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. 1. Open the workbook: Open the Excel workbook in WPS Spreadsheet and add or select a Summary sheet.
  2. 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. 3. Enter the link formula: Select B2 and enter =INDIRECT("'"&SUBSTITUTE($A2,"'","''")&"'!"&B$1).
  4. 4. Fill and check the results: Drag the fill handle across and down, then check that each summary value matches its source worksheet.
Fill one formula across and down to summarize multiple worksheetsOpen and edit Microsoft Excel XLSX workbooksUse a familiar spreadsheet interface with formula and fill-handle controls
microsoft office alternative - wps office

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.