Using ADDRESS in Excel

As promised, today I am going to cover how to use the ADDRESS function in Excel.

First a word on Excel address format.  Excel's format, and generally any other Spreadsheet software worth its salt, for addresses (or references, whatever you like to call them) is of the following format.

'Some\Path\To\File[actualfile.xlsx]Worksheetname'!A14:A26

So that's a single quote, followed by the path to the file (if it is not currently open), followed by the file name enclosed by brackets, followed by a single quote, followed by an exclamation mark, followed by the cell or range being referenced.  All of this is pretty important when using the address formula, I'll cover why here in just a second, but first let's look at the address formula.

Syntax of ADDRESS
row_num	column_num	abs_num	a1	sheet_txt
This is the row number that you wish to reference.  This is the row relative row 1.  So a one here would mean row 1.	This is the column number that you wish to reference.  This is the column relative to column A.  So a one here would mean column A.	This is one of those, you have to remember it kind of values.  It dictates the type of reference to return.  The easiest way to remember it is how the formula is laid out.  Row before column.	This is a boolean that specifies if you want A1 style or R1C1 style returned to you.  Again, like INDIRECT, true means A1 style, false means R1C! style.  Default is true.	Basically, if you put some text here, then the address that will be returned will have an exclamation mark, automatically added and the text found here will be placed in front of the exclamation mark.

Here are the options for the abs_num parameter:

Value	Meaning
1	All absolute.  So basically the return will be something like $A$1.
2	Just row absolute.  So basically the return will be something like A$1.
3	Just column absolute.  So basically the return will be something like $A1.
4	All relative.  Return will be A1.

By default if you give no value for abs_num it will assume that you mean all absolute. So this all sounds good, let's take a look at an example:

	A	B	C
1	3	3
2
3	76	41	26
4
5
6	=ADDRESS(A1,B1)	=INDIRECT(A6)

The value of A6 here would be $C$3 and the value of B6 would be 26, since that is the value in $C$3.

As you can see from this example, we can now change the value of A1 or B1 to change the value that the formula in B6 will get.  This becomes very powerful if you want to create ranges that your end users can size up.  One can simply use =ADDRESS(A1,B1)&":"&ADDRESS(A2,B2) to get a range.  Now toss in a bit of SUM and let's see what happens.

	A	B	C
1	3	1
2	3	3
3	76	41	26
4
5
6	=ADDRESS(A1,B1)&":"&ADDRESS(A2,B2)	=SUM(INDIRECT(A6))

Here the value of A6 is $C$1:$C$3 and the value of B6 is 143, because that is the sum of the range of $C$1:$C$3.  I could have just reused the value in A1 to limit my sum to just a single row.  However, imagine what you can do with this.  Now you can build entry forms on your Excel sheets that allow the end user to type in (or using data validation, by a drop down list) the information that they are interested in, and it will go out and perform the needed formulas for them, without having to retype the formula or select a range.

Finally, if you want to get information from other places, not just on the sheet where the address formula is located, make sure you use the sheet_txt parameter.  You won't have to include the single quotes or the exclamation mark, but the rest you'll need to include if needed.  For example, if you are simply referencing another sheet in the workbook, you just need to give the sheet name.  However, if you are referencing a whole different file, you need to put in the whole shebang for the name.

Now that you know how to use this, you can use things like HYPERLINK, to create links to where the formula is getting its values, additionally, you can use the MATCH formula to change which row you want to get data from.  I'll show you a complete example of using MATCH and HYPERLINK with ADDRESS and INDIRECT next time.