Combine the Same Cells from Multiple Excel Worksheets
Question details
Create a master list containing the same five cells from each invoice worksheet.

- 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.
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.
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.
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.
In B2, enter =INDIRECT("'"&SUBSTITUTE(A2,"'","''")&"'!I10") to return I10 from the sheet named in A2. The SUBSTITUTE function handles apostrophes in sheet names.
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.
Select B2:F2 and drag the fill handle down alongside the sheet names in column A. Check several results against their source worksheets.

Import invoice worksheets with Power Query
Use Power Query when you want a refreshable master list from many sheets in a separate source workbook.
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. Open the invoice workbook: Open WPS Spreadsheet and use Open to select the Excel workbook containing the invoice sheets.
- 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. Enter the references: In B2, enter =INDIRECT("'"&SUBSTITUTE(A2,"'","''")&"'!I10"). In C2 through F2, repeat the formula with the other four cell addresses.
- 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.

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.




