Function Problems

How to Automatically Populate a Latest Update Summary Worksheet in Excel

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs to create an automated summary worksheet that retrieves and displays the most recent updates (including customer, project, date, and text) from multiple individual project worksheets.

Product: Excel
Device & OS: not provided
Scenario: Consolidating project updates scattered across multiple tracking sheets into a single master dashboard or summary view.
Observed behavior: The user is looking for an automated method to pull the most recent records without manually checking and copying the newest entries from each project sheet.

Before you start

Ensure that all your individual project worksheets share a consistent layout (e.g., dates always in Column A, updates always in Column B) and that you know the exact names of these sheets.

Solution 1Recommended

Use INDIRECT, MAX, and INDEX/MATCH Formulas

This method uses dynamic references to look up the most recent date in each project sheet and retrieves the corresponding update text automatically.

By listing your worksheet names in the summary sheet, you can use the INDIRECT function to dynamically reference the data inside each specific sheet. Then, the MAX function finds the most recent date, while INDEX and MATCH locate the matching update details.

List Project Worksheet Names

In your summary sheet, list the exact names of your project worksheets in Column A, starting from cell A2.

Extract the Latest Date

In cell B2 (Latest Date), enter the formula: =MAX(INDIRECT("'"&A2&"'!A:A")). This assumes the dates in your project sheets are located in Column A.

Extract the Update Text

In cell C2 (Update Text), enter the formula: =INDEX(INDIRECT("'"&A2&"'!B:B"), MATCH(B2, INDIRECT("'"&A2&"'!A:A"), 0)). This assumes your update texts are in Column B of the project sheets.

Apply to All Projects

Select cells B2 and C2, then drag the fill handle down to apply these formulas to the rest of the projects listed in Column A.

Handling Sheet Names with Spaces: The single quotes inside the INDIRECT formula ( "'" ) are essential if your worksheet names contain spaces. Without them, the formula will return a #REF! error.

Solution 2

Consolidate Multiple Sheets Using Power Query

Power Query is an advanced tool that allows you to combine multiple worksheets into one dataset, sort by date, and keep only the newest records.

Advanced Data Consolidation

Easily Consolidate Multiple Worksheets with WPS Spreadsheet

WPS Spreadsheet provides a seamless experience for managing complex data. With full support for advanced functions like INDIRECT, VLOOKUP, and built-in data aggregation tools, you can easily pull latest project updates into a master summary sheet without performance lags.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your multiple project tracking sheets.
2. Create a Summary Sheet: Click the '+' icon to add a new sheet, name it 'Summary', and list your project sheet names in the first column.
3. Insert Consolidation Formulas: Use the built-in Formula Wizard (under the Formulas tab) to insert your MAX and INDIRECT functions safely.
4. Populate the Data: Drag the formula cell downwards to instantly populate the latest dates and texts for all your listed projects.

Fully compatible with Microsoft Excel formulas, functions, and .xlsx file formats.Supports dynamic array formulas for efficient data retrieval across multiple sheets.Lightweight performance ensures smooth operation even with large master workbooks.Offers intuitive formula wizards to help construct complex INDIRECT and MATCH queries error-free.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my INDIRECT formula returning a #REF! error?

A #REF! error typically occurs if the worksheet name referenced in your summary sheet does not exactly match the actual tab name, or if the referenced sheet name contains spaces but the formula lacks the necessary single quotes around the sheet reference.

How do I fix the extracted date displaying as a random number?

Spreadsheet software stores dates as serial numbers. To display it correctly, select the cell with the extracted date, right-click, choose 'Format Cells', and select your preferred Date format.

What happens if a project has multiple updates on the same latest date?

The standard INDEX and MATCH combination will return the first update it encounters on that specific date. To retrieve the absolute last entry regardless of the date, you may need to write a formula that identifies the maximum row number containing data.

Can I automate this process without writing complex formulas?

Yes, if you prefer not to use formulas, you can use Power Query to append the sheets and remove duplicates, or you can record a macro that copies the last row of each sheet onto the summary page.

Excel & Data Problems