Increment Cell Reference with INDIRECT?
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
Yes, you can increment a cell reference with the INDIRECT function by building the reference text with a row number that you calculate, for example =INDIRECT("A"&ROW()) or =INDIRECT("A"&(ROW()-1)). The INDIRECT function turns a text string into a real cell reference, so you can use ROW(), COLUMN(), or a helper cell to generate the row or column number dynamically and thereby create a reference that increments as you drag or copy the formula.
Formula
The formula to increment a cell reference with INDIRECT is:
=INDIRECT("A" & (ROW() + offset))
Where:
- ROW() returns the row number of the cell where the formula sits.
- offset is a number you add to that row (can be positive, negative, or zero).
- “A” is the column letter (you can change it to any column).
- & concatenates the text and the number into a string like “A1”.
- INDIRECT converts that string into an actual cell reference.
For example, if you put =INDIRECT("A"&ROW()) in cell B2, it returns the value from A2. If you drag it down to B3, it becomes =INDIRECT("A"&ROW()) which returns A3. The reference increments automatically because ROW() changes as you move down.
Worked Example
Using the default values from the calculator above: start row = 1, step = 1. The formula "A" & (1 + 1) gives the text “A2”. So the incremented reference is A2. If you change the start row to 5 and step to 2, the result is “A7”.
In practice, you might use =INDIRECT("A"&ROW()-1) in cell B2 to get the value from A1. When you drag down, ROW() becomes 3, then 4, etc., so the references become A2, A3, etc. This is a common way to create a running list that pulls from a column above.
Why Use INDIRECT Instead of Direct References?
Normally, you would write =A1 and drag down to get A2, A3, etc. That works fine. But INDIRECT becomes useful when:
- You need to build the reference from parts (e.g., a column letter stored in a cell).
- You want the reference to change based on a calculation (like ROW() or a helper cell).
- You are working with dynamic ranges where the row number is not fixed.
- You want to reference a sheet name that is stored as text.
For example, if you have a list of sheet names in column D, you can use =INDIRECT(D1&"!A1") to pull A1 from each sheet. That is not possible with a direct reference.
How to Increment Columns Too
You can also increment the column letter using INDIRECT with the COLUMN() function or by converting a number to a letter. For instance, to get A1, B1, C1 as you drag right, you could use:
=INDIRECT(ADDRESS(1,COLUMN()))
Here, ADDRESS(1, COLUMN()) returns the cell address as text (like “A1”) based on the row and column numbers. COLUMN() returns the column number of the current cell, so dragging right increments it. This is a clean way to avoid manually converting numbers to letters.
Common Mistakes and Limitations
- Volatile function: INDIRECT is volatile, meaning it recalculates every time Excel recalculates anything. In large workbooks, this can slow down performance.
- Sheet name with spaces: If the sheet name contains spaces, you must enclose it in single quotes:
=INDIRECT("'My Sheet'!A1"). - Using ROW() incorrectly: ROW() without an argument returns the row of the cell containing the formula. If you need the row of a different cell, use ROW(A10) which returns 10.
- Dragging horizontally: ROW() does not change when you drag right, so you must use COLUMN() or a different method for column increments.
- Referencing a closed workbook: INDIRECT cannot reference a workbook that is not open.
Alternatives to INDIRECT
If you only need to increment row numbers, you can often avoid INDIRECT entirely:
- Direct references: Just write
=A1and drag down. This is the simplest and fastest. - INDEX:
=INDEX(A:A,ROW())returns the value in column A at the current row. INDEX is not volatile and is generally faster. - OFFSET:
=OFFSET(A1,ROW()-1,0)also works, but OFFSET is volatile too. - ADDRESS with INDIRECT: Combine ADDRESS and INDIRECT when you need to build a reference from row and column numbers.
In most cases, INDEX is a better choice than INDIRECT because it is non-volatile and easier to read. Use INDIRECT when you need to interpret text as a reference, such as when the sheet name or cell address is stored in a cell.
Frequently Asked Questions
How do I increment a cell reference by a fixed number in a formula?
You can use =INDIRECT("A"&(ROW()+5)) to always reference a cell 5 rows below the current row. Replace 5 with any number. If you need to increment by a variable stored in a cell, use =INDIRECT("A"&(ROW()+$B$1)) where B1 contains the step.
Why does my INDIRECT formula return #REF! error?
This usually means the text string does not form a valid reference. Check for typos, missing quotes, or sheet names with spaces that need single quotes. Also, if you are using ROW() inside a cell that is in a different row than expected, the reference may point to a non-existent cell.
Can I use INDIRECT to increment both row and column?
Yes. You can build a reference like =INDIRECT(ADDRESS(ROW(),COLUMN())) which returns the current cell. To increment both, add offsets: =INDIRECT(ADDRESS(ROW()+1,COLUMN()+1)).
Is there a non-volatile alternative to INDIRECT for incrementing?
Yes, use INDEX. For example, =INDEX(A:A,ROW()) gives the value in column A at the current row. INDEX is not volatile and recalculates only when its inputs change, which is better for large workbooks.
How do I increment a reference across sheets with INDIRECT?
If you have sheet names in a list, use =INDIRECT(A1&"!B2") where A1 contains the sheet name. Drag down to get each sheet’s B2. Remember to quote sheet names with spaces like ="'My Sheet'!B2".
Does INDIRECT work in Google Sheets the same way?
Yes, INDIRECT works similarly in Google Sheets. The syntax and behavior are the same, including the volatility. Google Sheets also supports ROW() and COLUMN() functions.
Related
Formula used
in t | Start row | - | 1 | 1 in s | Step | - | 1 | 1 out ref | Incremented reference | - | "A" & (t + s) | 0
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.