Combining INDIRECT with ADDRESS

We’ve previously covered how we can use any function to generate a text that could be a valid cell reference, both column and row part of the address, and then use the INDIRECT function to convert that text to a cell reference. This process can be greatly enhanced by the ADDRESS function.

 

The ADDRESS function returns a text string that represents the address of a particular cell.

Row, column, type of reference (locked or absolute), reference style (A1 or R1C1 style) and worksheet can be specified in the arguments.

 

The ADDRESS function syntax is as follows:

= ADDRESS ( row_num ; column_num ; reference_type ; reference_style ; “[workboook]worksheet” )

Row number is a numeric value that specifies the row to be used in the cell reference.

Column number is a numeric value that specifies the column to be used in the cell reference.

Reference type is a numeric value that specifies the type of reference to be used in the cell reference, with 1 representing absolute references. If omitted, absolute reference will also be used.

Reference style is a numeric value that specifies the style of reference to be used in the cell reference, with 1 representing A1 reference style. If omitted, A1 reference style will also be used.

Worksheet and potentially workbook part of the address are entered in the final argument, inside quotation marks, as text. Valid examples are “Sheet1” or “[Book1]Sheet1”. You will, however, not be able to reference closed workbooks in this way. If omitted, current worksheet will be referenced.

 

If targeted cell is in the same sheet, shortened syntax can also be used:

= ADDRESS ( row_num ; column_num )

 

Consider the following example:

ADDRESS function

In the column E, we have generated a number of text string that represent cell addresses, both inside and outside current worksheet / workbook.

We are always first specifying row, then column, then reference type, then reference style, and at the end worksheet and eventually workbook:

ADDRESS function syntax

Given that those are valid references, we can put our returns as arguments for the INDIRECT function, and return the figures in referenced cells:

ADDRESS function to INDIRECT function

Note the #REF! error in the H15 cell – as Book3 is currently closed, that reference is hence not valid!

 

We can combine those functions directly by nesting ADDRESS function inside of INDIRECT function, as shown in cell H17:

ADDRESS function nested inside INDIRECT function

 

We can also combine two ADDRESS function in a text string in order to generate a reference to a cell range. Once generated, that text string can be nested inside of INDIRECT function, and operations performed with it:

ADDRESS function cell ranges

Note that when calculating the final cell in the range (second ADDRESS function), we are not referencing the worksheet.

 

Now consider this practical example:

ADDRESS function data example

Every month, we are receiving YTD data in this structure, i.e., every month, a new column and possibly several rows are added if new users and teams appear.

What is our last YTD?

What is our last MTD?

What is our last YTD for a particular user?

What is our last MTD for a particular team?

Answers to such questions can easily be automated and momentarily available with the help of the INDIRECT ADDRESS returns combined with other needed functions:

ADDRESS function data analysis

Assuming the same data structure, by counting filled rows and columns we can generate indirect references to the last periods. Once set up, these kinds of, admittedly complex, formulas can provide us with the required answers indefinitely. In every future period, our only additional work would be to paste new export to the “data” sheet.

There are naturally other ways to achieve these results, but situationally, INDIRECT ADDRESS combination will be the simplest or even the most robust solution.

 

Dig deeper:

Text in Excel

5 thoughts on “Combining INDIRECT with ADDRESS”

  1. Instead of “RIGHT” on the array sum function, I removed the sheet name from the 2nd address
    =SUM(INDIRECT(ADDRESS(1,2,1,1,”Sheet2″)&”:”&ADDRESS(10,2,1,1)))

  2. I’m trying this function but error is been popped

    primary Formula –

    =IFERROR(INDEX(‘[SAP JUN-19.XLSX]BOM Working’!$A:$A, xxxxxxxx))

    Formula using INDIRECT-

    =IFERROR(INDIRECT(““”&”INDEX(‘[SAP “&’Working Sheet-‘!B5&”.XLSX]BOM Working&”‘”&”!”&$A:$A”),xxxxxxx))

    Cell reference –

    ‘Working Sheet-‘!B5

    Error –

    There is a problem with this formula,
    Not trying to type a Formula?
    When the first character is an equal (“=”) or minus (“-) sign, Excel thinks it’s a formula.

    Please help.

    1. If I correctly understood what you were trying to accomplish, try structuring your formulas in this way:

      =INDEX(INDIRECT(ADDRESS(1,1,1,1,”[Book1]Sheet1″)&”:”&ADDRESS(10000,1,1,1)),xxxxxxx)

      =INDEX(INDIRECT(ADDRESS(1,1,1,1,”[Book1]”&A6)&”:”&ADDRESS(10000,1,1,1)),xxxxxxx)

      Br,

Leave a Reply