Make INDIRECT References Increase When Dragging a Formula Down in WPS Spreadsheets
Question details
The user needs to make an INDIRECT formula increase its referenced row number when filled down instead of keeping a static text row number.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a formula combining a fixed column reference from cell C75 with a row number stored or written as text in INDIRECT, and wants to drag it down a column.
- Observed behavior
- The row number inside the INDIRECT reference does not update automatically, so each filled cell keeps pointing to the same row.
Confirm that cell C75 contains the column letter or reference you want to keep fixed and that you are starting the formula in the first row that should be referenced, such as row 77.
Use the ROW function inside INDIRECT to create a dynamic row reference
Use this solution when the row number inside an INDIRECT reference is static and the formula should change row by row as it is filled down.
INDIRECT treats its text argument literally, so a plain text row number does not increase during a fill operation.
Inserting ROW(A77) as the row portion returns 77 in the first cell and increases as the formula is copied down.
Click the cell where the formula should start, then type =COUNTIF(E77:INDIRECT($C$75&ROW(A77)),1)/COUNT(E77:INDIRECT($C$75&ROW(A77))) and press Enter.
Drag the fill handle from the bottom-right corner of the cell down the column to apply the formula to the cells below.
Select each filled cell and confirm that ROW(A77) changes to ROW(A78), ROW(A79), and so on, producing the equivalent row ranges E78:INDIRECT($C$75&78), E79:INDIRECT($C$75&79), etc.
Make INDIRECT References Increase Dynamically with ROW in WPS Spreadsheets
Use the ROW function inside INDIRECT in WPS Spreadsheets to make row references increase automatically when you fill a formula down, while keeping fixed column references unchanged.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the Excel-compatible workbook that contains the INDIRECT formula.
- 2. Select the first formula cell: Click the cell where the COUNTIF/COUNT formula should begin, for example E77.
- 3. Enter the dynamic INDIRECT formula: Type =COUNTIF(E77:INDIRECT($C$75&ROW(A77)),1)/COUNT(E77:INDIRECT($C$75&ROW(A77))) and press Enter.
- 4. Fill the formula down: Drag the fill handle down the column so ROW(A77) becomes ROW(A78), ROW(A79), and so on.
- 5. Check the updated ranges: Confirm that the E77:INDIRECT($C$75&ROW(A77)) range changes to E78:INDIRECT($C$75&ROW(A78)), E79:INDIRECT($C$75&ROW(A79)), etc.
Frequently Asked Questions
Why doesn't INDIRECT update when I drag the formula down?
INDIRECT treats its text argument literally. If the row number is written as plain text, it stays the same in each filled cell. Use ROW(A77) to return a row number that increases as the formula is filled down.
How do I keep the column reference fixed while the row increases?
Store the column in an absolute reference such as $C$75 and concatenate it with ROW(A77). The dollar signs keep the column fixed, while ROW updates to A78, A79, and so on.
Can I use this with SUM or AVERAGE instead of COUNTIF and COUNT?
Yes. Replace COUNTIF and COUNT with your intended functions, and keep the range INDIRECT($C$75&ROW(A77)) so the row number continues to update when you fill down.
Will this formula work the same in Microsoft Excel and WPS Spreadsheets?
Yes. INDIRECT and ROW are supported in both applications, so the formula behavior is compatible and the workbook can be opened in either program.
