How to Fix Excel Summary Sheet Not Pulling Data from Other Worksheets
Question details
The user needs to fix a project cost workbook where a Summary sheet is failing to pull expected data from other worksheets.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Aggregating data from multiple source worksheets (e.g., Meeting Room sheets) into a master Summary sheet.
- Observed behavior
- The Summary sheet is missing expected values, likely because formulas are referencing source cells that have been deleted, missing cost fields, or renamed without updating.
Before troubleshooting, ensure all source worksheets are present in the workbook and have not been renamed without updating the dependent formulas.
Inspect and Reconstruct Broken Worksheet References
Identify missing source data or broken links by tracing the formula references back to the source worksheets and fixing the cell paths.
When a summary sheet fails to pull data, it is usually because the source cell was deleted, a column was removed, or the worksheet name was changed. Reconstructing the formula using adjacent working formulas is the most reliable fix.
Click on the cell in the Summary sheet that is failing to pull the expected data or displaying an error.
Review the exact formula in the formula bar. Check if it references the correct worksheet name and cell range (e.g., ='Meeting Room 1'!B12). Look for a #REF! error which indicates a deleted source cell.
Navigate to the referenced worksheet (e.g., Meeting Room 1) and check if the target data actually exists. If only other data is present, the target cell you are trying to pull may have been accidentally deleted.
Look at adjacent cells in the Summary sheet that are successfully pulling data. Analyze their formula structure to understand the workbook's intended mapping.
Clear the broken reference in the formula bar, type '=', then click directly on the correct source worksheet tab and select the appropriate target cell to establish a fresh, working link. Press Enter to confirm.
Easily Consolidate Worksheet Data with WPS Spreadsheet
WPS Office provides intuitive formula tracing tools to help you pull and consolidate data across multiple worksheets effortlessly. It easily identifies broken links and is fully compatible with your existing Excel files.
- 1. Open your workbook in WPS: Launch WPS Office and open your .xlsx project cost workbook.
- 2. Use Trace Precedents: Go to the 'Formulas' tab and click 'Trace Precedents' to visually see exactly where your Summary sheet data is coming from.
- 3. Re-link source cells easily: Type '=' in the Summary sheet, then simply click the source tab and select the desired cell to automatically create perfectly formatted cross-sheet references without typing them manually.
Frequently Asked Questions
Why is my Excel formula showing #REF! when pulling data from another sheet?
A #REF! error occurs when a formula refers to a cell that is not valid. This usually happens if the source cell, column, row, or even the entire source worksheet was deleted or moved after the formula was created.
Do I need to keep source workbooks open for formulas to work?
If the source data is on a different sheet within the same workbook, no. However, if your Summary sheet pulls data from an entirely different external workbook, keeping both workbooks open ensures the links update smoothly and prevents #VALUE! errors.
How do I correctly reference a sheet name with spaces?
When a worksheet name contains spaces, you must enclose the sheet name in single quotes in your formula. For example: =SUM('Meeting Room 1'!A1:A10).
Can I automatically pull data from multiple sheets into a summary?
Yes, you can use 3D references (e.g., =SUM(Sheet1:Sheet3!A1)) to aggregate the same cell across multiple sheets, or use the 'Consolidate' tool under the Data tab to automatically pull and calculate data from uniformly structured sheets.
