Data Import & Export

Combine the Same Cells from Multiple Excel Worksheets

Steven ParkerSteven Parker Nov 18, 2026 868 views

Question details

Create a master list containing the same five cells from each invoice worksheet.

Combine the Same Cells from Multiple Excel Worksheets
Product
Microsoft Excel
Device & OS
not provided
Scenario
Many invoice worksheets use a consistent layout, and the user wants one row of selected values per worksheet.
Observed behavior
The desired result is a master list with a separate column for each of the five invoice cells.
Before you start

Confirm that the five values appear at the same cell addresses on every invoice sheet. Identify the invoice sheet names and decide whether the master list will be in the source workbook or a separate workbook.

Solution 1Recommended

Use INDIRECT formulas with a list of worksheet names

Use this method when you can maintain a list of worksheet names and the invoice layout is consistent.

1
List the invoice sheets

Create a master worksheet and enter each invoice sheet name in column A, starting at A2. Enter the names exactly as they appear on the sheet tabs.

2
Reference the first cell

In B2, enter =INDIRECT("'"&SUBSTITUTE(A2,"'","''")&"'!I10") to return I10 from the sheet named in A2. The SUBSTITUTE function handles apostrophes in sheet names.

3
Add the other four cells

In C2 through F2, use the same formula but replace I10 with each of the other four invoice cell addresses. Add descriptive headers in row 1.

4
Fill the formulas down

Select B2:F2 and drag the fill handle down alongside the sheet names in column A. Check several results against their source worksheets.

Use INDIRECT formulas with a list of worksheet names
Keep the sheet list current: Add new sheet names to column A and extend the formulas when invoices are added. INDIRECT is volatile, so a large number of formulas may slow recalculation.
Combine invoice cells in WPS

Create an invoice master list in WPS Spreadsheet

Use sheet names and INDIRECT formulas in WPS Spreadsheet to collect the same five invoice cells into a master worksheet.

  1. 1. Open the invoice workbook: Open WPS Spreadsheet and use Open to select the Excel workbook containing the invoice sheets.
  2. 2. Create a master sheet: Add a worksheet using the plus button beside the sheet tabs, then list the invoice sheet names in column A starting at A2.
  3. 3. Enter the references: In B2, enter =INDIRECT("'"&SUBSTITUTE(A2,"'","''")&"'!I10"). In C2 through F2, repeat the formula with the other four cell addresses.
  4. 4. Fill and check the list: Select B2:F2 and drag the fill handle down for all listed sheets. Compare a few rows with the original invoice tabs, then save the workbook.
Bring five values from each invoice sheet into one listOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceGet started with the free version of WPS Office
microsoft office alternative - wps office

Frequently Asked Questions

How do I handle sheet names with spaces or apostrophes?

Enter the exact sheet name in column A and use =INDIRECT("'"&SUBSTITUTE(A2,"'","''")&"'!I10"). The single quotes surround the sheet name, and SUBSTITUTE doubles any apostrophes within it.

Can a 3D reference create the five-column master list?

A 3D reference can aggregate the same cell across contiguous sheets, for example with =SUM(Sheet1:Sheet5!I10), but it does not conveniently produce one row per sheet with five separate values.

Why does my INDIRECT formula return a reference error?

Check that the name in column A exactly matches an existing sheet tab and that the cell address in the formula is valid. Also check for extra spaces in the sheet name.

Will Power Query automatically include new invoice sheets?

After you save new sheets in the source workbook, use Data > Refresh All in the master workbook. The query will include them only if they match its sheet filters and expected layout.