Function Address()

Address()


This function is used to custom build a address range in Excel. It converts a text into actual address based on given parameters. The function can return address for a single cell and a range of cell as well.




Syntax:


= Address (Row_num, Column_num, Reference_type, Address_type, Sheet_name)


Row_num: This value determines the row number that the address should refer to
Column_num: This value determines the column number that should be referred by the address
Reference_type: This single-digit parameter determines which type of reference should the address have. The reference codes are - 1=absolute reference, 2=absolute row/relative column reference, 3=relative row/absolute column reference and 4=relative reference. The default reference is 'Absolute' reference
Address_type: This value determines the style of address that should be returned. There is no effect of this parameter on its functioning. The address type codes are - 1 or TRUE = A1 type reference, 0 or FALSE = R1C1 style reference. In case this parameter is not available, the function adopts the A1 style reference, even if R1C1 is the default style in the sheet
Sheet_name: As the name suggests, this parameter provides the sheet name which should be included in the address as its part. This is used when the address should refer to a range which is external to the given sheet


Note: In the above syntax, Reference type, Address Type and Sheet name are optional parameters. Any of these can be mentioned in the function independent of each other




Examples:


Here are a few examples of using Address() function

• Address(1,2) - result will be: $B$1
• Address(2,1) - result will be: $A$2
• Address(2,1,1) - result will be: $A$2
• Address(2,1,2) - result will be: A$2 (note the reference style, the reference to column 'B' is not absolute)
• Address(2,1,4) - result will be: A2
• Address(2,1,1,1) - result will be: $A$2
• Address(2,1,1,TRUE) - result will be: $A$2
• Address(2,1,1,0) - result will be: R2C1
• Address(2,1,1,FALSE) - result will be: R2C1
• Address(2,1,1,1,Sheet1) - result will be: Sheet!$A$2 (note that the address now contains sheet name also)
• Address(2,1,1,1,Sheet_new) - result will be: Sheet_new!$A$2
• Address(2,1,,1) - result will be: $A$2 (note that the third parameter has been ignore, but its place holder remains there as a blank indicated by 2 commas)

Application:

The address function is generally used where a cell range is used, but the range is dynamic in itself. Mostly, it used with the Indirect() function. The Indirect() function is used to convert the text address into actual reference.

For example, in a worksheet, a column should populate values which are dependent on a certain condition. Now this requires the range of cells to be dynamic in the way that it should dynamically change based on the condition mentioned. Here is an example of the application of Address() function with Indirect() function.

Share on Facebook Share on Twitter Share on Google Plus

About Dhakkanz

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.