How to Create an Excel Reference by Adding Rows to a Previous Reference in WPS Spreadsheets
Question details
The user needs an Excel formula that creates cell references by adding a fixed number of rows to a previous reference, such as returning H21, H58, and H95 in sequence.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Building a formula that returns values from cells spaced at a fixed row interval without manually typing each reference.
- Observed behavior
- A standard formula does not automatically advance by the desired fixed row interval when copied, so a dynamic reference formula is needed.
Confirm the starting row, the fixed row interval, and the cell that will hold the counter value before entering the INDIRECT formula. Also verify the exact sheet name and column you want to reference.
Add a fixed row interval with INDIRECT and a counter cell
Use this when you have a starting row, a fixed step such as 37, and a counter value to generate references like H21, H58, and H95 automatically.
INDIRECT builds a cell reference from text, so you can calculate the row number instead of typing each row manually.
Click the cell where the first result, such as H21, should be returned.
Type =INDIRECT("Tracker!H"&(21+37*(C1-1))) and press Enter. Replace Tracker!H with the actual sheet name and column.
Drag the fill handle across cells that contain counter values in the counter row, so C1 changes to D1, E1, and so on.
Build the next row reference from the current ROW position
Use this when the current formula row already represents the starting row and you want the next result to be offset by a fixed number of rows.
Create Dynamic Excel References in WPS Spreadsheets
You can build row references that increase by a fixed interval directly in WPS Spreadsheets using INDIRECT. Enter the formula with a counter, set the starting row and increment, and copy it across to return values from rows such as 21, 58, and 95 without retyping references.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the .xlsx workbook containing the Tracker sheet or the data you want to reference.
- 2. Select the output cell: Click the first cell where you want the indexed result to appear, such as the cell beside your first counter value.
- 3. Enter the dynamic INDIRECT formula: Type =INDIRECT("Tracker!H"&(21+37*(C1-1))) and press Enter. Replace Tracker!H, 21, 37, and C1 with your sheet, column, starting row, step, and counter cell.
- 4. Copy the formula across the counter row: Drag the fill handle across the cells that contain counter values to generate H21, H58, H95, and following references automatically.
Frequently Asked Questions
How do I change the starting row in the INDIRECT formula?
Replace 21 in =INDIRECT("Tracker!H"&(21+37*(C1-1))) with your actual starting row number. For example, use 10 if your first reference should be H10.
How can I make the formula return results from every 37 rows instead of every row?
Change 37 to your desired step. The formula uses 37*(C1-1), so when the counter increases by 1, the row number increases by 37.
Can I use a different worksheet name or column in the reference?
Yes. Replace Tracker!H with the actual sheet name and column reference, such as Sales!B. If the sheet name has spaces, wrap the sheet name in single quotes in the text string.
Why does my INDIRECT formula return #REF!?
The most common cause is that the sheet name or cell reference does not exist. Check that the sheet name is spelled exactly, the column exists, and the calculated row number is within the worksheet.
Can I use this INDIRECT formula in both WPS Spreadsheets and Microsoft Excel?
Yes, INDIRECT works in both WPS Spreadsheets and Excel, and WPS Spreadsheets preserves formulas when you save and share .xlsx files.
