ADDRESS

ADDRESS(row_num, column_num [,abs_num] [,a1] [,sheet_name])

Returns the cell reference given a row and column number.

row_numThe row number to use in the cell reference between 1 and 65,536.
column_numThe column number to use in the cell reference between 1 and 256.
abs_num(Optional) The number indicating the type of reference to return:
1 = absolute ($A$1) (default)
2 = absolute row/relative column (A$1)
3 = relative row/absolute column ($A1)
4 = relative (A1)
a1(Optional) A logical value indicating whether to return A1 or R1C1 reference notation:
True (<> 0) = A1 notation is returned (default)
False (or 0) = R1C1 notation is returned
sheet_name(Optional) The name of the worksheet to be used as the external reference (active sheet)

REMARKS
* If "row_num" < 1, then #VALUE! is returned.
* If "col_num" < 1, then #VALUE! is returned.
* If "abs_num" is left blank, then 1 is used.
* If "a1" = True then the A1 style reference is returned.
* If "a1" = False then the R1C1 style reference is returned.
* If "a1" is left blank, then True is used.
* If "sheet_name" is left blank, then the cell reference will refer to the active sheet.
* The "sheet_name" does not have to be the name of a worksheet that actually exists.
* The "row_num" can be a cell reference that evaluates to a number.
* The "col_num" can be a cell reference that evaluates to a number.
* This function will always be single threaded when the "sheet_name" is provided.
* You can use the COLUMN function to return the column number of a cell reference.
* You can use the INDIRECT function to return the value of a given cell reference specified by a text string.
* You can use the ROW function to return the row number of a cell reference.
* You can use the SUBSTITUTE function to return a text string after replacing instances of a substring.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

 AB
1=ADDRESS(2, 3) = $C$25
2=ADDRESS(2, 3, 1) = $C$210
3=ADDRESS(2, 3, 2) = C$2Ge0eral
4=ADDRESS(2, 3, 3) = $C2 
5=ADDRESS(2, 3, 4) = C2 
6=ADDRESS(B1, 3, 4) = C5 
7=ADDRESS(2, 3, 2, FALSE) = R2C[3] 
8=ADDRESS(2, 3, 1, FALSE, "[Book1]Sheet Name") = '[Book1]Sheet Name'!R2C3 
9=ADDRESS(2, 2, 2, 1, "worksheet_name") = worksheet_name!B$2 
10=ADDRESS(ROW(named_range), COLUMN(named_range)) = B2 
11=ADDRESS(-1, 2) = #VALUE! 

1 - What is the cell reference at row 2, column 3.
2 - What is the absolute cell reference at row 2, column 3.
3 - What is the absolute row, relative column cell reference at row 2, column 3.
4 - What is the relative row, absolute column cell reference at row 2, column 3.
5 - What is the relative cell reference at row 2, column 3.
6 - What is the relative cell reference at the row number in cell B1, column 3.
7 - What is the absolute row, relative column cell reference displayed in R1C1 notation at row 2, column 3.
8 - What is the absolute row, relative column cell reference displayed in R1C1 notation at row 2, column 3 and prefixed with the sheet name "[Book1]Sheet Name".
9 - What is the absolute row, relative column cell reference displayed in A1 (a1 > 0, so True) at row 2, column 2 and prefixed with the sheet name "worksheet_name".
10 - What is the cell reference of a named range. In this example cell "B2" has been given the named range "named_range".
11 - What is the cell reference at row -1, column 2.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top