Using ADDRESS function

Lets look at the syntax first.

Syntax

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

1. row_num can be specified by numerical or by row() function. 

2. column_num can be specified by numerical or  by column() function.

3. abs_num has 4 choices,

abs_num	Returns this type of reference
1 or omitted	Absolute (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.)
2	Absolute row; relative column
3	Relative row; absolute column
4	Relative

4. a1 has two options, TRUE, 1 or omitted and FALSE or 0. TRUE, 1 or omitted will give A1 style and FALSE will give R1C1 style. 

5. sheet_text specifies the sheet name in which the cell is located. Please note sheet names are case sensitive.

Lets look at the different possibilities.

Formula	Results
=ADDRESS(1,1)	$A$1
=ADDRESS(1,1,1)	$A$1
=ADDRESS(1,1,1,1)	$A$1
=ADDRESS(1,1,1,0)	R1C1
=ADDRESS(1,1,1,1,"Sheet1")	Sheet1!$A$1
=ADDRESS(ROW(A1),COLUMN(A1),1,1,"Sheet1")	Sheet1!$A$1
=ADDRESS(1,1,1,1,"Sheet1")	Sheet1!$A$1
=ADDRESS(1,1,2,1,"Sheet1")	Sheet1!A$1
=ADDRESS(1,1,3,1,"Sheet1")	Sheet1!$A1
=ADDRESS(1,1,4,1,"Sheet1")	Sheet1!A1

Simply put this function will give the cell number. So whats the big deal about a cell number. Well it can be used in labeling.

Example

Look at the example below,

It works fine till a row or column added or removed. 

Now the label become meaningless as name should be entered in E3. 

The solution is to use ADDRESS function.

="Please enter the name in cell "&ADDRESS(ROW(E2),COLUMN(E2),4,)

It works fine even a row or column added or removed. By this way the label auto adjusts.

Add a row...

Add a column...

The label adjusts automatically.

In the same way a range can be specified in a label. Using formula as shown below,

="Please enter the name in cell "&ADDRESS(ROW(F3),COLUMN(F3),4,)&":"&ADDRESS(ROW(F20),COLUMN(F20),4,) 

Love to have your comments.