Function Problems

Increment INDIRECT Cell References When Dragging Formulas in Excel

Steven ParkerSteven Parker Nov 14, 2026 869 views

Question details

Make an INDIRECT reference advance to the next source row when a formula is copied down.

Increment INDIRECT Cell References When Dragging Formulas in Excel
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.
Before you start

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.

Solution 1Recommended

Use ROW to increment the INDIRECT reference

Build the row number dynamically so it changes as you drag the formula down.

1
Enter the formula

In the destination cell, enter =INDIRECT("'Enter Permit'!A"&ROW()) if the destination row matches the source row.

2
Adjust the starting 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.

3
Fill the formula down

Select the formula cell and drag its fill handle down. In the offset example, the next formula references A7, followed by A8.

Check the worksheet name: Replace Enter Permit with the actual source worksheet name. Keep single quotes around a sheet name that contains spaces.
Work with formulas in WPS Spreadsheets

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. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and select the destination worksheet.
  2. 2. Enter the formula: Click the first destination cell and enter =INDIRECT("'Enter Permit'!A"&ROW()) when its row matches the source row.
  3. 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. 4. Fill down: Select the formula cell and drag its fill handle down to reference successive source rows.
Increment source rows automatically with INDIRECT and ROWOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceWork with formulas in the free WPS Office suite
microsoft office alternative - wps office

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.