How to Restore and Update an Excel Workbook with Multiple Years of Data
Question details
The user needs to update a complex Excel workbook that aggregates multi-year actual data from various roll-up sheets and templates into a main comparison sheet.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Updating a complex financial or property data tracking workbook to properly calculate and display selections across a three-year span.
- Observed behavior
- The main analysis sheet needs to accurately reflect metrics like EGI, OpEx, and NOI based on dynamic property and year selections across multiple data tabs.
Before making structural changes, ensure that all source sheets have a consistent column layout to prevent formula mismatch errors.
Map Data and Expand Formulas for Multiple Years
Identify your roll-up sheets and manually expand your lookup formulas and data validations to incorporate the new years of data.
To properly integrate new years of data into a complex comparison sheet, you must trace the relationships between your source sheets and the main dashboard.
Consistency in the data structure across all sheets is crucial to ensure formulas like INDEX and MATCH can dynamically retrieve values based on user selections.
Locate the source sheets, such as Roll-Up Data, Templates, and Property View. Identify which columns contain properties, years, and specific metrics (like EGI, OpEx, and NOI).
Enter the data for the required multiple years (e.g., 2021, 2022, and 2023) into the appropriate source sheets, ensuring consistent formatting across all new entries.
Select the main comparison sheet and update the data ranges in your formulas (such as INDEX/MATCH, XLOOKUP, or SUMIFS) so they span the newly added columns or rows.
Go to Data > Data Validation, locate the cells containing your year dropdowns, and expand the source range to include the newly added years.
Navigate to Home > Conditional Formatting > Manage Rules. Update the 'Applies to' ranges to ensure the new multi-year data is formatted correctly.
Select various combinations of properties and years on your main sheet and manually verify the results against the source data to ensure accuracy.

Easily Manage Complex Multi-Year Workbooks with WPS Office
WPS Spreadsheet fully supports advanced array functions and multi-sheet consolidation, making it easy to handle complex roll-ups and financial templates without compatibility issues.
- 1. Open your workbook: Launch WPS Spreadsheet and open your complex multi-year workbook.
- 2. Audit and update formulas: Navigate to the Formulas tab to trace precedents and seamlessly edit your INDEX/MATCH or XLOOKUP formulas.
- 3. Manage dropdowns: Use the Data Validation tool in the Data tab to easily update your property and year selection dropdowns.
- 4. Save with full compatibility: Save your updated file in .xlsx format to ensure it remains perfectly functional across all Office platforms.

Frequently Asked Questions
Why are my INDEX/MATCH formulas returning #N/A after adding new years of data?
This usually happens because the formula's array reference was strictly defined and not expanded to include the newly added columns or rows. Check the formula and adjust the references, or format your data as a Table to make ranges dynamic.
How can I automatically include new years in my drop-down list?
You can create a dynamic named range using the OFFSET formula, or format your list of years as a Table and base your Data Validation source on that Table column.
Can I use Power Query to consolidate multiple years of roll-up data?
Yes. Power Query can append multiple yearly data tables into a single source table, significantly simplifying the lookup formulas required on your main comparison sheet.
What is the best formula to look up data based on both a property name and a year?
A two-way lookup using INDEX and MATCH is highly effective. Alternatively, you can use XLOOKUP nested within another XLOOKUP to return the exact metric for a specific intersection of a property and a year.




