How to Get the Latest Status from Project Sheets Using Excel Formulas
Question details
Retrieve the newest status update from various project worksheets based on a master list of project names.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a project overview sheet that automatically pulls the most recent status updates from individual project tabs.
- Observed behavior
- The user needs a dynamic formula to look up and return the latest entry from identically formatted project sheets without manual copying.
Ensure that the project names listed in your overview sheet exactly match the names of the individual project worksheet tabs, and that each sheet follows a consistent data layout (e.g., dates in column G, status in column H).
Use INDIRECT and VLOOKUP to Retrieve the Latest Data
Combine INDIRECT, MAX, and VLOOKUP functions to dynamically reference sheet names and return the most recent status based on the maximum date.
By nesting INDIRECT inside a VLOOKUP and MAX function, you can dynamically refer to different worksheets without hardcoding the sheet names. This is especially useful for master tracker dashboards.
In your overview sheet, ensure your project names are listed in a specific column, for example, Column A starting from cell A2.
Click on the cell where you want the latest status to appear, such as F2.
Input the formula: =VLOOKUP(MAX(INDIRECT("'"&A2&"'!G5:G100")),INDIRECT("'"&A2&"'!G5:H100"),2,0). This assumes dates are in column G and statuses in column H.
Press Enter to apply the formula. Then, drag the fill handle from the bottom-right corner of F2 down to apply the formula to the rest of the project list.
Easily Manage Multi-Sheet Project Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, including INDIRECT, MAX, and VLOOKUP, allowing you to seamlessly manage complex multi-sheet project trackers for free.
- 1. Open your workbook: Launch WPS Spreadsheet and open your multi-sheet project tracker file.
- 2. Navigate to the overview sheet: Switch to the master overview tab where you want to consolidate the latest statuses.
- 3. Apply the lookup formula: Use the exact same =VLOOKUP(MAX(INDIRECT(...))) formula provided, as WPS Spreadsheet perfectly parses Excel's syntax.
- 4. Save your work: Drag down the formula to fill your tracker, then save the document seamlessly in .xlsx format.
Frequently Asked Questions
Why is my INDIRECT formula returning a #REF! error?
A #REF! error typically occurs if the worksheet name referenced in your formula contains typos, does not exist, or has extra hidden spaces. Double-check that the names in your overview sheet exactly match the actual sheet tab names.
What if the individual project sheet names contain spaces?
The provided formula automatically handles spaces in sheet names. The single quotes (') placed around the dynamically generated sheet name ("'"&A2&"'!...") ensure that references to names like 'Project Alpha' are read correctly by Excel.
Can I use XLOOKUP instead of VLOOKUP for this task?
Yes. If your spreadsheet software supports XLOOKUP, you can combine it with INDIRECT and MAX to find the latest date and return the corresponding status. XLOOKUP is often more flexible since the status column does not need to be to the right of the date column.
