Create an Excel Summary List from the Same Cell on Multiple Sheets
Question details
Create a summary list containing worksheet names and the value from the same cell on multiple similarly structured vehicle worksheets.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to compile matching cell values from many vehicle sheets and have the list include worksheets added later.
- Observed behavior
- A summary is needed that can return values from the same cell across a sheet range and identify the worksheet that each value came from.
Save the workbook first, because the CELL("filename",A1) formula returns a sheet name only after the file has a saved path. Keep the source worksheets in a continuous tab order and use the same target cell, such as A1, on every worksheet.
Use TEXTJOIN and TEXTSPLIT to collect one cell from a range of worksheets
Use this method in Excel versions that support TEXTJOIN and TEXTSPLIT to return the value from the same cell on every sheet between two named boundary sheets.
A 3D sheet reference includes every worksheet between the first and last sheet named in the reference. Sheets inserted between those boundary tabs can be included automatically when they use the same layout.
In the workbook tab bar, place the vehicle worksheets between two boundary tabs, such as Sheet1 and LastSheetName. The sheets to include must remain between these tabs.
On the summary worksheet, select the first blank cell where the collected values should appear, such as A2.
Enter =TEXTSPLIT(TEXTJOIN(";",TRUE,'Sheet1:LastSheetName'!A1),,";") and press Enter. Replace A1 with the matching source cell used on each vehicle worksheet.
Insert each new vehicle worksheet between Sheet1 and LastSheetName, then ensure its target cell contains the required value. The 3D reference can include it in the summary range.
Return each worksheet name with CELL and filter names by a condition
Use this method when the summary should identify the source sheet or include only worksheets that meet a specific condition.
Build a Multi-Sheet Cell Summary in WPS Spreadsheets
WPS Spreadsheets lets you organize similarly structured worksheets and build a summary table from repeated cell locations. Use consistent source-cell positions, place sheets in a defined range, and use supported spreadsheet formulas to consolidate and review the results in one worksheet.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the existing Excel workbook containing the vehicle worksheets.
- 2. Create a summary worksheet: Click the plus button beside the sheet tabs to add a worksheet, then name it Summary.
- 3. Standardize the source cell: Check that every vehicle worksheet stores the needed value in the same location, such as cell A1, before creating the summary.
- 4. Add consolidation formulas: Select a destination cell on the Summary sheet and enter a supported formula that references the matching cell across the worksheet range.
Frequently Asked Questions
How do I show the current Excel worksheet name in a cell?
Save the workbook, then enter =TEXTAFTER(CELL("filename",A1),"]") in a cell on that worksheet. The CELL formula reads the file and sheet information, and TEXTAFTER returns the text after the closing bracket.
Will a 3D Excel reference include sheets added later?
It can include new worksheets when they are inserted between the first and last boundary sheets in a reference such as 'Sheet1:LastSheetName'!A1. Sheets outside that tab range are not included.
Can I collect a cell from every sheet when some cells are blank?
Yes. In =TEXTJOIN(";",TRUE,'Sheet1:LastSheetName'!A1), the TRUE argument tells TEXTJOIN to ignore empty values before TEXTSPLIT returns the results as separate rows.
Why does the CELL filename formula not show my sheet name?
CELL("filename",A1) needs a saved workbook path. Use File > Save or press Ctrl+S, then recalculate the worksheet if necessary.
How can I include only vehicle sheets marked Automatic steering?
On each vehicle sheet, use =IF(B1="Automatic steering",TEXTAFTER(CELL("filename",A1),"]"),"") in a helper cell, then consolidate that helper cell from the sheet range on the summary worksheet.
