How to Create a Dynamic INDIRECT Reference with ADDRESS in Excel
Question details
The user wants to construct a dynamic cell reference using the INDIRECT and ADDRESS functions so that the reference updates correctly when the formula is copied across rows or columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building flexible spreadsheet models where cell references need to shift dynamically without relying on hardcoded text strings.
- Observed behavior
- Successfully generating a dynamic cell reference that adapts to row or column changes when dragging the formula handle, without requiring extra quotation marks.
Verify that your spreadsheet layout is stable before implementing this formula. Keep in mind that the INDIRECT function is a volatile function, meaning it recalculates every time the worksheet calculates, which may impact performance on very large files.
Combine INDIRECT, ADDRESS, ROW, and COLUMN Functions
Use a nested formula to dynamically generate a text-based cell address and immediately convert it into a functioning reference.
The ADDRESS function builds a cell reference as a text string based on given row and column numbers. By nesting the ROW() and COLUMN() functions inside ADDRESS, the numeric coordinates update dynamically when you copy the formula. Finally, INDIRECT translates this text string back into a valid cell reference that Excel can read.
Click on the cell where you want the dynamic formula to generate results, such as cell B1.
Type the formula =INDIRECT(ADDRESS(ROW(A1),COLUMN(A1))) directly into the formula bar at the top of the window.
Press the Enter key. The cell will evaluate the reference pointing to cell A1.
Click and hold the small square fill handle at the bottom-right corner of cell B1, then drag it down the column. The reference inside the formula will dynamically change from A1 to A2, A3, etc.
Create Dynamic References Effortlessly in WPS Office
WPS Spreadsheet fully supports advanced array and reference functions, including INDIRECT, ADDRESS, ROW, and COLUMN. You can build complex, dynamic financial models and data trackers completely for free.
- 1. Open your worksheet: Launch WPS Office and open your .xlsx or .xls file in WPS Spreadsheet.
- 2. Access the Function Wizard: Click the 'Formulas' tab on the top ribbon and select 'Insert Function' to easily search for INDIRECT and ADDRESS.
- 3. Input the dynamic formula: Enter the exact formula =INDIRECT(ADDRESS(ROW(A1),COLUMN(A1))) into your chosen cell and press Enter.
- 4. Fill your data automatically: Drag the green fill handle at the corner of your cell down your column or across rows to apply the dynamic reference universally.
Frequently Asked Questions
Why does my INDIRECT function return a #REF! error?
This error usually happens if the text string generated by the ADDRESS function doesn't point to a valid cell, or if it points to an external workbook that is currently closed. Double-check your ROW and COLUMN inputs to ensure they calculate valid cell coordinates within the open workbook.
Can I reference another worksheet dynamically using INDIRECT and ADDRESS?
Yes. You can include the sheet name directly in the ADDRESS function's arguments, or concatenate it before the ADDRESS result natively. For example: =INDIRECT("'Sheet2'!" & ADDRESS(ROW(A1),COLUMN(A1))). This allows you to pull data dynamically from other tabs without breaking the reference.
Is there a performance impact when using INDIRECT and ADDRESS heavily?
Yes. The INDIRECT function is 'volatile', meaning the spreadsheet engine recalculates it every single time any change is made to the workbook. If applied across tens of thousands of rows, it can slow down recalculation times. If performance drops, consider using the non-volatile INDEX function as an alternative.
Why does my cell reference break when I insert a new row above my data?
If you hardcode row numbers directly into the ADDRESS function, inserting a new row won't automatically update those numeric values. Using ROW(A1) instead of typing the number 1 ensures the relative position is tracked. However, extreme structural changes might still disrupt volatile INDIRECT references.
