Automatically Update Excel RSQ and SUMSQ Formulas When Sample Ranges Change
Question details
Create formulas that automatically adjust their input ranges when daily in-sample and out-of-sample row boundaries change.

- Product
- Microsoft Excel formulas
- Device & OS
- not provided
- Scenario
- The user maintains a table of dates and corresponding start and end row numbers for changing sample periods.
- Observed behavior
- RSQ, SUMSQ, and similar formulas require manual range edits whenever the sample row ranges change.
Set up a structured lookup table containing a Current Date column plus separate start-row and end-row columns for each sample period. Confirm that the selected date in the worksheet exactly matches a date in that table.
Use INDEX and XLOOKUP to create dynamic formula range endpoints
Use this method when the start and end rows are stored in a date-based table and calculations must update automatically as those row numbers change.
INDEX returns a cell reference at a specified row, while XLOOKUP retrieves the correct start and end row for the selected date. Combining them with a colon creates a dynamic range that functions in RSQ, SUMSQ, and other range-based formulas.
In your worksheet, create an Excel table with columns such as Current Date, In Sample Start, and In Sample End. Enter the applicable start and end row numbers for each date.
Enter or select the active date in a cell such as A1. This date is used by XLOOKUP to find the correct row boundaries in the table.
For the first RSQ input range in column M, enter: INDEX(M:M,XLOOKUP(A1,Table[Current Date],Table[In Sample Start])):INDEX(M:M,XLOOKUP(A1,Table[Current Date],Table[In Sample End])).
For the matching values in column G, use the same pattern and combine both ranges: =RSQ(INDEX(M:M,XLOOKUP(A1,Table[Current Date],Table[In Sample Start])):INDEX(M:M,XLOOKUP(A1,Table[Current Date],Table[In Sample End])),INDEX(G:G,XLOOKUP(A1,Table[Current Date],Table[In Sample Start])):INDEX(G:G,XLOOKUP(A1,Table[Current Date],Table[In Sample End]))).
Replace RSQ with functions such as SUMSQ where appropriate, while keeping the INDEX and XLOOKUP range endpoints. Update only the table row values when a sample period changes.

Use a helper cell for each dynamic start and end row
Use helper cells when you want to inspect the retrieved row numbers or make complex formulas easier to audit.
Build automatically updating sample-range formulas in WPS Spreadsheet
WPS Spreadsheet lets you create date-driven formulas with INDEX and XLOOKUP, so statistical and calculation ranges can follow changing sample boundaries instead of being edited manually each day.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the source data and sample-range table.
- 2. Format the boundary data as a table: Select the date, start-row, and end-row data, then use the table option on the Insert tab to organize the lookup values.
- 3. Enter the selected date: Click the date input cell, such as A1, and enter the date whose sample range should be calculated.
- 4. Insert the dynamic calculation formula: Click the result cell and enter an INDEX and XLOOKUP formula that returns the start and end references for the required columns.
- 5. Update row limits in the table as needed: When sample boundaries change, edit the relevant start or end row value in the table and allow WPS Spreadsheet to recalculate the formula.

Frequently Asked Questions
Why use INDEX instead of INDIRECT for dynamic row ranges?
INDEX returns a reference without converting text into a reference, making it generally more suitable for building dynamic endpoints such as INDEX(M:M,startRow):INDEX(M:M,endRow).
Can I use the same dynamic-range method with SUMSQ?
Yes. For example, use =SUMSQ(INDEX(M:M,startRow):INDEX(M:M,endRow)), where startRow and endRow are returned by XLOOKUP or stored in helper cells.
What happens if XLOOKUP cannot find the selected date?
XLOOKUP returns an error when A1 has no matching value in Table[Current Date]. Check that both values are true dates, not mismatched text, and that the date exists in the lookup table.
Do both RSQ ranges need to have the same number of rows?
Yes. The two arrays supplied to RSQ must cover matching observations. Make sure the start and end row lookups produce equivalent row counts for both columns.
How can I create separate in-sample and out-of-sample formulas?
Add separate columns such as Out of Sample Start and Out of Sample End to the lookup table. Use those columns in the XLOOKUP return-array arguments for the out-of-sample formula.




