Excel Tip of the Week #314 - ADDRESS and INDIRECT redux

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.

Hello all and welcome back to the Excel Tip of the Week!  This week, we have a Developer level post in which we're taking a fresh look at the reference-building functions ADDRESS and INDIRECT.  These were last covered back in TOTW #132.

What is ADDRESS all about?

Firstly, let's take ADDRESS.  This is a function that creates a text output that resembles a cell reference.  So if for example you want to generate a reference to cell B4 on a sheet that you specify, you might do this:

The syntax breaks down like this:

=ADDRESS(row number, column number, absolute option, referencing style, sheet name)

Row number is exactly that - which number row you want.  Of course this could be a formula of its own; however in our example this is just a 4 for row 4.
Column number is slightly trickier - although we're used to using letters to denote columns, this must be a number - in our example, a 2 for column B.
Absolute option and referencing style are provided if you care about absolute referencing (B4 vs $B$4) or if you want to use the R1C1 reference style (see TOTW #137).  However, these are both unlikely to be necessary and usually I would just skip them.
Sheet name is our final input - specifying the sheet name we want to use.  If blank, you'll just get a reference to the current sheet.  Here we're using a cell reference to pick up the sheet named in the dropdown menu in cell C5.

You can also make ranges using this method, by using an & to join together two ADDRESS formulas with a colon:

We specify a sheet for the first ADDRESS only.

Adding in INDIRECT

Now, the above formulas only create text that looks like a reference.  Excel still treats the result as text and doesn't actually involve the cell named.

What INDIRECT does is take a text input and ask Excel to look at it as though it were a cell reference - i.e. to try and actually go find the cell in question.  Here's our first example converted in to an INDIRECT that actually retrieves the value of cell B4 on the stated sheet:

If we want to use the range-style option we introduced above, of course we will need to feed the resulting range into some kind of summary function:

INDIRECT is essential for using ADDRESS - it lets you do things like pulling up a number from a sheet relating to a specific person, using that person's name to steer the ADDRESS to the desired sheet.  But you don't actually need ADDRESS to use INDIRECT - it can work with anything that Excel could interpret as a reference.

For example, if you have a named range, then INDIRECT can be used to get Excel to recognise that range in places it doesn't normally expect it.  For example, let's say we want to use a dropdown to choose which of three named ranges will be summed up:

We can also use this when working with dropdowns (and see TOTW #296 for more on those) - so for example, we can set which dropdown appears in one cell based on what has been chosen in a previous cell:

And finally, you can use INDIRECT to dynamically refer to cells within an Excel table by column name, by building up a structured reference (see TOTW #164):

You can see all these examples in the file attached at the bottom of this post.

Previous post - Revisiting find & replace
TOTW index 
Next post - Google Sheets: Counting distinct items

ADDRESS and INDIRECT redux.xlsx