Formula Errors

How to Dynamically Update Rows in INDEX and AGGREGATE Formulas

Khadija Khan Sep 30, 2026 869 views

Question details

The user needs to make INDEX and AGGREGATE formula row references dynamic to prevent #NUM! errors across varying report start cells, and wants to pull sheet names dynamically using the INDIRECT function.

Dynamically Update Rows in INDEX and AGGREGATE Formulas to Fix #NUM! Errors

Product: Spreadsheet
Device & OS: not provided
Scenario: Creating dynamic report summaries from multiple worksheets where the data ranges do not always begin at the same cell.
Observed behavior: The complex formula successfully returns data for the first report but outputs a #NUM! error for subsequent reports due to static row dependencies.

Before you start

Ensure your spreadsheet software is up-to-date and supports dynamic array functions. Identifying the exact row structure of your varying reports beforehand will help you construct an accurate dynamic reference.

Solution 1Recommended

Replace INDEX and AGGREGATE with VSTACK and FILTER

Modern dynamic array formulas like VSTACK and FILTER are much more reliable for combining multi-sheet reports than complex INDEX/AGGREGATE combinations.

Instead of struggling with complex row calculations that break when the starting cell changes, you can simply stack the ranges vertically and filter out the blanks. This avoids #NUM! errors entirely.

Stack the data ranges

Type `=VSTACK(Sheet1!A2:D100, Sheet2!A2:D100)` to combine ranges from multiple worksheets into a single vertical array.

Apply the FILTER function

Wrap the VSTACK formula with FILTER to exclude empty rows. For example, write `=FILTER(VSTACK(Sheet1!A2:D100, Sheet2!A2:D100), VSTACK(Sheet1!A2:A100, Sheet2!A2:A100)<>"")`.

Execute the dynamic formula

Press Enter to spill the combined and filtered data into your destination report automatically without having to drag the formula down manually.

Replace INDEX and AGGREGATE with VSTACK and FILTER

Dynamic Arrays Advantage: Using VSTACK and FILTER completely eliminates the need to manually update starting cell rows, making your reports fully dynamic and future-proof.

Solution 2

Use INDIRECT to Dynamically Select Worksheets

Construct a dynamic worksheet reference using a cell value (e.g., from Column A) instead of hardcoding the sheet name in your formula.

Solution 3

Standardize the Row Array in AGGREGATE Formulas

If you are restricted to using AGGREGATE, ensure the row divisor evaluates sequentially from 1, regardless of where the data starts.

Powerful Spreadsheet Software

Easily Manage Dynamic Array Formulas in WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like VSTACK, FILTER, and INDIRECT. This makes it incredibly simple to consolidate multiple reports dynamically without running into frustrating #NUM! errors.

1. Open your multi-sheet workbook: Launch WPS Spreadsheet and open the file containing your various data reports.
2. Insert dynamic functions: Click into your summary dashboard cell and enter a modern dynamic array formula like `=FILTER(VSTACK(...))` to gather all your data seamlessly.
3. Automate sheet referencing: Use the INDIRECT function linked to a dropdown list in column A to dynamically switch between sheets without rewriting formulas.

100% compatibility with Microsoft Excel's advanced formulas, functions, and file formats.Built-in dynamic array capabilities for robust and automated data analysis.Lightweight application that processes heavy, multi-sheet reports smoothly.Intuitive error tracing tools to help you quickly diagnose and resolve #NUM! and #REF! formula errors.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my AGGREGATE formula return a #NUM! error when dragged down?

This typically occurs because the 'k' argument (often represented by a ROWS() function) exceeds the number of valid items returning from your criteria array, or because the criteria array results entirely in errors. Using a relative row calculation ensures valid numbers are consistently passed to the function.

Can the INDIRECT function be used inside INDEX and AGGREGATE?

Yes, you can wrap INDIRECT inside your INDEX and AGGREGATE formulas to dynamically reference ranges across different sheets based on a cell's value. Keep in mind that INDIRECT is a volatile function, meaning it recalculates every time the sheet updates, which may slow down large workbooks.

Are VSTACK and FILTER better than INDEX and AGGREGATE?

For combining and filtering data, VSTACK and FILTER are significantly more modern, easier to write, and less prone to #NUM! errors. They automatically spill results into adjacent cells, eliminating the need to drag complex formulas down your spreadsheet.

Excel & Data Problems