Vergel’s Excel and Visual Basic Blog

The ADDRESS() Function

March 8th, 2007

Given a specific row and column number, the ADDRESS() function can give you the cell address as text.

The syntax for the ADDRESS() function is:

ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

row_num and column_num are the only required parameters. They identify the row number and column number respectively.

abs_num is used to identify reference type to use in the return value. If omitted, it defaults to 1.
1 or omitted = Absolute reference
2 = Absolute row, relative column
3 = Relative row, absolute column
4 = Relative reference

A1 is a logical value used to identify the reference style to use in the return value. If omitted, it defaults to TRUE and returns the address in A1 style reference. If FALSE, the return value will be in R1C1 style reference.

Sheet_text is a string value that is used to identify the sheet name to be used in the return value.

Below are examples of how to use the ADDRESS() function. In the samples, Colors is a named range that refers to A2:A5.

Download the sample workbook here .

Posted in Excel, Functions | No Comments