Dynamic References: OFFSET, INDIRECT and ADDRESS

Data OrganizationAnalyticsDecision SupportModelling & Forecasting8 min readMicrosoft 365 for Windows

The problem

A formula normally points at a fixed place: =SUM(B2:B13) always adds the same twelve cells. That is exactly what you want until the question changes. Add the last three months for whichever product is selected. Report the total of the sheet named in a cell. Take the year to date up to the month typed in a header. In each case the place to look is not known when the formula is written; it depends on an input.

Excel solves this with functions that build a reference while the formula is calculated. Three of them do the work: OFFSET moves from a starting point, ADDRESS writes a cell address as text, and INDIRECT turns text into a live reference. They are powerful, they are widely used in older models, and they come with a cost that every user should know before relying on them.

The examples use the companion file F12_Dynamic_References.xlsx. The Sales sheet holds twelve months of net sales (thousands) for three products, in A1:D13. The US, UK and DE sheets each hold the monthly net sales of one market, in A1:B13.

A reference is not a value

When you type B4 in a formula, you point at a cell and Excel reads whatever it holds. A reference is therefore a different thing from the value it points to, and several functions can produce one: SUM accepts B2:B13, but also anything that returns that range.

Dynamic references use this fact in two ways. Some functions compute a reference from a starting point and a number of rows and columns, which is what OFFSET does. Others take a piece of text that looks like a reference, and turn it into the real thing, which is the task of INDIRECT, with ADDRESS as the tool that writes the text. In both cases the reference is decided at calculation time, by cells you can change.

ADDRESS: writing an address as text

ADDRESS returns the address of a single cell, as text, from a row number and a column number:

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

The abs_num argument controls the dollar signs: 1 or omitted gives an absolute address, 2 an absolute row and a relative column, 3 the opposite, and 4 a fully relative one. In the ADDRESS INDIRECT sheet the inputs sit in B2:B8, with row 4, column 2 and reference type 2 in B2:B4. Then:

=ADDRESS(B2,B3,B4)             returns B$4
=ADDRESS(B2,B3,B4,B6,B5)       returns UK!B$4
=ADDRESS(B2,B3,B4,NOT(B6))     returns R4C[2]

The second formula adds the sheet name taken from B5. The third switches the a1 argument off, which makes ADDRESS write the address in R1C1 style, where R stands for row and C for column. This notation, which describes cells by their position and not by a column letter, comes from Multiplan, the 1982 spreadsheet from Microsoft that preceded Excel.

ADDRESS on its own is rarely useful, because it produces text: a formula cannot read the cell B$4 from a text that says so. It becomes useful when it feeds INDIRECT.

INDIRECT: turning text into a reference

INDIRECT takes a text and interprets it as a reference:

=INDIRECT(ref_text, [a1])

If the second argument is TRUE or omitted, the text is read in the usual A1 style; if FALSE, in R1C1 style. When the text is not a valid reference, the function returns #REF!. In the same sheet, three formulas show its uses.

The first reads the cell whose address ADDRESS has just written:

=INDIRECT(ADDRESS(B2,B3,1,TRUE,B5))

With row 4, column 2 and the sheet UK, it returns 311, the UK net sales of March.

The second adds a range whose first and last rows are inputs (B7 and B8, rows 3 and 8). ADDRESS writes both ends, and the colon joins them into a range:

=SUM(INDIRECT(ADDRESS(B7,B3,1,TRUE,B5)&":"&ADDRESS(B8,B3)))

It returns 1,933, the sum of the UK sales from rows 3 to 8. Change the rows and the same formula adds something else.

The third use is the best known: choosing a sheet by name. With UK in B5:

=SUM(INDIRECT(B5&"!B2:B13"))

builds the text UK!B2:B13 and adds it up, giving 3,990. Replace UK with DE and the formula reads the other sheet. If a sheet name contains spaces, the name inside the text must be enclosed in single quotes: "'"&B5&"'!B2:B13".

This is how a consolidation can be built without a single sheet reference typed in a formula. Rows 21 to 23 list the markets in A21:A23, and column B holds:

=SUM(INDIRECT(A21&"!B2:B13"))

copied down. The results are 18,056 for the US, 3,990 for the UK and 5,389 for Germany, and column C checks them with ordinary references. Adding a market is a matter of copying a sheet and typing its name in the list.

ADDRESS writes both ends as text, INDIRECT turns them into a live range: change B7 or B8 and the sum moves.

OFFSET: moving from a starting point

OFFSET starts from a reference and moves it:

=OFFSET(reference, rows, cols, [height], [width])

The first argument is the anchor. Rows and columns say how far to move, down and to the right when positive, up and to the left when negative. The optional height and width give the size of the result; when they are omitted the result is as big as the anchor. If the movement takes the result outside the worksheet, OFFSET returns #REF!.

In the OFFSET sheet, the inputs are in B2:B6: 3 rows, 2 columns, the last 3 months, the product Cardexa and the month Jul-25.

=OFFSET(Sales!$A$1,$B$2,$B$3)

starts at A1 of the Sales sheet, moves three rows down and two columns right, and lands on C4. That is the Cardexa figure for March, 419. Note that rows and columns count steps from the anchor, not row numbers: the anchor is the header row, so three steps down is the third month.

Function Arguments for OFFSET: anchor, rows and columns; with Height and Width empty the result is a single cell.

The real strength appears when the movement is itself calculated. Three examples, all driven by the product and month inputs and the helper MATCH($B$5,Sales!$B$1:$D$1,0), which finds the product column:

=OFFSET(Sales!$A$1,MATCH($B$6,Sales!$A$2:$A$13,0),MATCH($B$5,Sales!$B$1:$D$1,0))

is a lookup: two MATCH calls find the row of the month and the column of the product, and OFFSET returns the cell at their crossing, 427 for Cardexa in July. The second one is a sliding window, the sum of the last N months:

=SUM(OFFSET(Sales!$A$1,COUNT(Sales!$B$2:$B$100)-$B$4+1,MATCH($B$5,Sales!$B$1:$D$1,0),$B$4,1))

COUNT finds how many months have been entered, so that the window begins N months before the last one. The height is N, the width 1, and SUM adds the result: 1,368 for the last three months of Cardexa. Type a thirteenth month in row 14, and the formula slides on by itself; the range is read up to row 100 precisely to leave room. The count assumes that the months are entered without gaps.

The third one is the year to date: the same anchor, with a height equal to the position of the selected month, which returns 3,028 for Cardexa from January to July.

=SUM(OFFSET(Sales!$A$1,1,MATCH($B$5,Sales!$B$1:$D$1,0),MATCH($B$6,Sales!$A$2:$A$13,0),1))

Whenever the result of OFFSET is more than one cell it must be used inside a function that expects a range, such as SUM or AVERAGE. In Microsoft 365, a multi-cell OFFSET typed on its own spills its values into the cells around it; older versions returned #VALUE!.

Each OFFSET result in column B is matched by a non-volatile INDEX version in column C.

The cost: volatility

OFFSET and INDIRECT are volatile functions. Most formulas recalculate only when something they depend on changes; volatile ones recalculate every time Excel recalculates anything, because Excel cannot know in advance what they point at. Microsoft's documentation lists OFFSET and INDIRECT together with TODAY and NOW. In a small file nobody notices. In a model with thousands of such formulas, every edit triggers a long recalculation, and everything that depends on them recalculates too.

For this reason the OFFSET sheet puts an INDEX version next to each OFFSET formula, in column C. INDEX can return a reference as well, which means it can be used on both sides of a colon:

=SUM(INDEX(Sales!$B$2:$D$100,COUNT(Sales!$B$2:$B$100)-$B$4+1,MATCH($B$5,Sales!$B$1:$D$1,0)):INDEX(Sales!$B$2:$D$100,COUNT(Sales!$B$2:$B$100),MATCH($B$5,Sales!$B$1:$D$1,0)))

It is longer and less readable, but it returns the same 1,368 and is not volatile. The same holds for the lookup and the year to date, rows 9, 10 and 12. As a rule of thumb, use OFFSET and INDIRECT for models of modest size, or when no alternative exists, and look at INDEX first when a model is large. There is no real non-volatile equivalent of INDIRECT for choosing a sheet by name, so keep such formulas few and put them in one place.

Common mistakes

Counting rows as sheet rows. In OFFSET the rows and columns are steps from the anchor: 0 means the anchor itself.
Moving outside the worksheet, for example a negative result beyond row 1, which returns #REF!.
Expecting ADDRESS to return a value. It returns text, and needs INDIRECT to become a reference.
Renaming a sheet and forgetting that INDIRECT works on text. The text is not updated and the formula returns #REF!.
Omitting the single quotes around a sheet name that contains spaces.
Pointing INDIRECT at a workbook that is closed. Excel returns #REF! until the file is open.
Using OFFSET and INDIRECT freely in a large model and wondering why every edit takes seconds.
Using the last-N pattern on data with gaps. COUNT does not count empty cells, and the window ends too early.