Increment INDIRECT Cell References When Dragging Formulas in Excel
Question details
Make an INDIRECT reference advance to the next source row when a formula is copied down.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to drag a formula down while referencing successive cells on the Enter Permit worksheet.
- Observed behavior
- A row number written inside the INDIRECT text string stays fixed when the formula is copied.
Check the source worksheet name and the row containing the first source cell. Note the row where you will enter the first formula so you can calculate any required offset.
Use ROW to increment the INDIRECT reference
Build the row number dynamically so it changes as you drag the formula down.
In the destination cell, enter =INDIRECT("'Enter Permit'!A"&ROW()) if the destination row matches the source row.
If the rows differ, add or subtract the difference inside ROW's expression. For example, in destination row 1, enter =INDIRECT("'Enter Permit'!A"&(ROW()+5)) to reference source cell A6.
Select the formula cell and drag its fill handle down. In the offset example, the next formula references A7, followed by A8.
Increment INDIRECT references in WPS Spreadsheets
Use INDIRECT and ROW in WPS Spreadsheets to build a reference that advances as you fill a formula down.
- 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and select the destination worksheet.
- 2. Enter the formula: Click the first destination cell and enter =INDIRECT("'Enter Permit'!A"&ROW()) when its row matches the source row.
- 3. Set an offset if needed: If the first formula is in row 1 and the source starts at A6, change the formula to =INDIRECT("'Enter Permit'!A"&(ROW()+5)).
- 4. Fill down: Select the formula cell and drag its fill handle down to reference successive source rows.

Frequently Asked Questions
Why doesn't INDIRECT change A6 to A7 when I drag the formula?
In =INDIRECT("'Enter Permit'!A6"), A6 is part of a text string, so Excel does not adjust it as a cell reference. Build the row number with ROW() instead.
How do I start at source row 6 when my formula is in row 1?
Enter =INDIRECT("'Enter Permit'!A"&(ROW()+5)) in row 1. The offset makes the first reference A6, and dragging down advances it to A7 and beyond.
What if my source sheet has a different name?
Replace Enter Permit in the formula with the actual worksheet name. For a name containing spaces, keep the single quotes around it, as in =INDIRECT("'My Sheet'!A"&ROW()).
Can I use a negative row offset?
Yes. Subtract the difference when the source row is above the destination row. For example, =INDIRECT("'Enter Permit'!A"&(ROW()-2)) entered in row 8 references A6.




