Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
What’s Your Address?
Excel’s ADDRESS() function is used to create a text representation of a worksheet cell address. Let’s see how it works first and then we’ll take a look at some situations where this capability might be useful.
At its simplest, ADDRESS() requires two arguments: a row number and a column number, in that order. For example, the function ADDRESS(2,4) returns the text address "$D$2"—fourth column, second row—without the quotes, of course. Note that the address returned is by default absolute, as indicated by the dollar symbol before both the column letter and the row number. If you do not want an absolute address you include the optional third argument which can be one of the following numbers:
Third argument to ADDRESS() |
Type of address returned |
|
1 (or omitted) |
Absolute ($D$2) |
|
2 |
Row absolute, column relative (D$2) |
|
3 |
Column absolute, row relative ($D2) |
|
4 |
Relative (D2) |
The fourth argument, also optional, is a True/False value that tells ADDRESS() whether you want the more common A1-style address, where the column is indicated by a letter, or the R1C1-style address when both the row and column are indicated by numbers. Using True for this argument, or omitting it, results in the A1 style, whereas a value of False gives you the R1C1 style. Thus, ADDRESS(2,4,1,FALSE) returns the text "R2C4". Please note the following:
- When you include this argument, you must also include any earlier optional arguments even if you want to use their default value. That’s why I included the third argument 1 in this function even though 1 is the default. This is of course true for all argument and all Excel functions.
- The R1C1 address format uses brackets to indicate relative addresses. Thus, R2C4 is an absolute address, R2C[4] is row absolute and column relative, and so on.
The final argument to the ADDRESS() function is an optional workbook/worksheet reference. If included as an argument, it will be part of the returned address. For instance, ADDRESS(2,4,1,TRUE,"[data.xls]Sheet1") returns the text "[data.xls]Sheet1!$D$2". The ADDRESS() function does add the required exclamation point, but it does not check to see whether you have specified a valid reference.
You may have already realized that the ADDRESS() and INDIRECT() functions are natural partners. Using ADDRESS() to determine the address of a cell of interest and then using INDIRECT() to retrieve its contents can be very useful in many situations. For example, look at the simple table of sales results shown in the following figure.
)
You can see that the total is calculated in cell C8, and you want to refer to that total elsewhere in the worksheet. The problem is that rows may be added to or removed from the table as branch offices are opened or closed, so you cannot simply refer to $C$8; you need a way to refer to "the cell in column C just below the last row of data." Assuming that the data table range, B2:C7, has been assigned the name Sales, here’s how:
=INDIRECT(ADDRESS(ROW(Sales) + ROWS(Sales), COLUMN(Sales)+1))
Here’s how this works:
- ROW(Sales) returns the row number at the top of the Sales range.
- ROWS(Sales) returns the number of rows in the range. By adding this to the starting row you get the row number of the first row just below the range, where the total is displayed.
- COLUMN(Sales) returns the starting column of the range Sales. By adding 1 to this you get the column where the total is displayed.
- The ADDRESS() function turns this information into a cell address.
- The INDIRECT() function retrieves the value in the specified cell.
Yes, I know this seems a bit complicated, but the ADDRESS() and INDIRECT() functions can provide you with flexibility that is not available any other way.